cancel
Showing results for 
Search instead for 
Did you mean: 

Replace Null(?) values with 0 in CE script based CV !!

0 Kudos

Hi Gurus,

I have a CE script based Calculation view calculation view.

In which the count of a particular field is calculated based on a filter. Suppose if the filter value is not present for a row

then count field is getting null(?) values for that row in the view output.

Now i want to replace those null(?) values with number 0.

CE_CALC is not working on the calculated or dynamic fields, it is working only on the table fields.

Please help on this.

Thanks in Advance,

Devi.

Accepted Solutions (0)

Answers (5)

Answers (5)

0 Kudos

Hi Safiyu,

The column Z is present in var_loadcase it was a typo.

Anyways done the coding using SQL script due to time constrain.

But will try the option. Thanks for the help .

Regards,
Devi.

0 Kudos

Hi Safiyu and Krishna,

please find my code below,

Based on the filter value im getting a count of another field COUNTRESULT. If the filter value is not present then null values is coming up in the output.

VAR_LOADCASE       = CE_PROJECTION (:IT_BASE,["X","Y_RESULT"],'("Y_RESULT" = ''YES'')');

VAR_LOADCASE_COUNT = CE_AGGREGATION (:VAR_LOADCASE, [COUNT("Z") as "COUNTRESULT"], ["X"]);

So if use IFNULL or IF condition on the COUNTRESULT field getting syntax error saying "Attribute not found in Column Table".

Thanks,

Devi.

former_member210482
Active Participant
0 Kudos

Hi Devi,

From where are you getting "z" colum. your var_loadcase doesnt have column z. So it will give this error attribute not found. And also

VAR_LOADCASE = CE_PROJECTION (:IT_BASE,["X","Y_RESULT"],"Y_RESULT" = 'YES'); is enough right. Why so many quotes. Any particular reason?


Also in this case, create a calculated column in your aggregation node where you can write a if condition.


ce_calc('if("count"='',0,1)',INTEGER) as "CNT" in ce_projection.


Hope this helps.


Regards,

Safiyu

0 Kudos


Hi Safiyu and Krishna,

The IF conditions are not working on the dynamic coulmns. It is working on the source columns only so i couldnt check for my calcluated count field.

thanks,
Devi.

former_member210482
Active Participant
0 Kudos

Hi,

Can you share your dynamic column code

Regards,

Safiyu

former_member182302
Active Contributor
0 Kudos

Hi Devi,

You are using Dynamic columns in Calculation View (READ ONLY)?

Can you share more details?

Regards,

Krishna Tangudu

former_member182302
Active Contributor
0 Kudos

Use IFNULL

Regards,

Krishna Tangudu

former_member210482
Active Participant
0 Kudos

Hi Devi,

You mean to say you have already created a calculated column count and you are getting null values when filter value is not present? If that is the case use if() condition

something like if(filter,1,0). If my understand is wrong please correct me.

Regards,

Safiyu