topic Re: null values in Qlik Automate

null values

inam — Wed, 12 Jul 2023 10:24:51 GMT

Hello Community

in my data I found blank value So I want all my blank value merge into actual data.

current output:-

here I found 58 blank data in Dom holiday FIT so i want to apply below formula for each field(direct, email + ma, organic, paid & referral)

for example:-

(16*58)/506

16 = Direct total as per the report

58 = No source data (blank data)

506 = total of direct, email + ma, organic, paid & referral

how I apply this formula in my script? I have given only 1 date ..I have to calculate for 1 month.

Re: null values

marcus_sommer — Wed, 12 Jul 2023 11:27:14 GMT

You need to assign a real value to these NULL to be able to access these data. This might be done within the script per:

if(len(trim(PROD_TYPE)), PROD_TYPE, 'no Data') as PROD_TYPE

and then in the chart-expression something like:

count(PROD_TYPE) * count({< PROD_TYPE = {'no Data'}>} total <PRODUCT> PROD_TYPE) /
count(total <PRODUCT> PROD_TYPE)

Re: null values

udit_k — Wed, 12 Jul 2023 11:40:39 GMT

use below expressions:-

Re: null values

inam — Wed, 12 Jul 2023 12:47:40 GMT

Thank for the reply

I am using pivot table please can you help same as pivot table . how I use this formula in pivot table

Re: null values

inam — Thu, 13 Jul 2023 05:01:46 GMT

Hello @marcus_sommer Thank for the help .

i have used this formula & its working fine.

formula :- =count(PROD_TYPE)+count(PROD_TYPE) * count({< PROD_TYPE = {''}>}
total <[Product Type]> PROD_TYPE) /count(total <[Product Type]> PROD_TYPE)

but I got null value .can you please help how I remove this 65 & 37 below values

so if I remove 65 i want total should be 499. same as if 37 remove total should be 205

I have used backend script below

Re: null values

marcus_sommer — Thu, 13 Jul 2023 06:31:13 GMT

I'm not sure if {< PROD_TYPE = {''}>} would fetch the created empty string. You may try instead {< PROD_TYPE = {'*'}>} or {< PROD_TYPE = {"*"}>}. Personally I wouldn't create an empty string even if you could access them everywhere like you want because it's rather confusing and not the entire truth. Like mentioned specifying it as 'no Data' or 'NULL' and if there were various sources/causes including them too as information.

Beside of this what's really the aim of this view and which further views might be touched? In the above scenario it might be an alternatively to keep the missing values as NULL and to hide this NULL within the chart-option for the dimension.

Otherwise I suggest to put each single calculation into an own expression to see which one return the expected results and if then combining them step by step into further separate expressions to continue the validation if the parts are added in the right order - you may need to wrap some parts with brackets to determine the wanted order.

Re: null values

inam — Thu, 13 Jul 2023 07:39:17 GMT

Hello @marcus_sommer

'No data' value is coming from Raw data. i want 1st this 'no data' value is calculate according to formula and then 2nd is when the value is calculating correctly then remove 'No data' value. is this possible ?

I am using pivot Table .

i want to remove only 'No data' values or 'No data' rows after calculate according to formula. do you have any alternative solution ?

Re: null values

udit_k — Thu, 13 Jul 2023 08:09:46 GMT

The expression posted earlier is in pivot table

Re: null values

marcus_sommer — Thu, 13 Jul 2023 08:31:02 GMT

Conclusion: no data shouldn't be displayed as rows within the chart but the numbers should be available within any total calculation? If yes, you may use expressions like those one:

normal count:
count({< PROD_TYPE -= {'no Data'}>} PROD_TYPE)

total count (same value for all rows) + a boolean check to exclude the no data rows
count(total <[Prod Type]> PROD_TYPE) * sign(count({< PROD_TYPE -= {'no Data'}>} PROD_TYPE))

Re: null values

inam — Thu, 13 Jul 2023 08:41:31 GMT

hello @udit_k

formula working fine but i want to remove only 'No data' values or 'No data' rows after calculate according to formula. do you have any alternative solution ?

as your solution null value is also count as i see in your Total. how i remove null values?

Re: null values

udit_k — Thu, 13 Jul 2023 08:53:36 GMT

write below expression in Text Color expression of each measure:-

if(Len(Region)<=0,white())

Re: null values

inam — Thu, 13 Jul 2023 10:16:00 GMT

Hiii @udit_k

I used below its not work.

& also i uesd your formula but if I use if(Len(Region)<=0,white()) i am getting whole value white (direct, email + ma, organic, paid & referral)

and if i used this how i remove Total Value ? 65 i want total should be 499. same as if 37 remove total should be 205?

Re: null values

udit_k — Thu, 13 Jul 2023 10:41:34 GMT

Hi , We can't Hide that row , but we could have workaround by using expression.

Use below expression for final calculation:-

(Sum({<Region={"=len(Region)>0"}>}Count)*sum(total<USER,Region>{<Region={"=len(Region)<1"}>} Count))
/

sum(total<USER>Count)

so here Region having null data, that row calculation is to Zero and it doesn't calculate in total as well.

Re: null values

inam — Thu, 13 Jul 2023 12:01:56 GMT

hii @udit_k

i am using same your formula in straight table but not getting value

1 column :- COUNT([PROD_TYPE])

2column :- sum(total<PROD_TYPE,Region>{<Region={'=len(Region)<1'}>}PROD_TYPE)

3 column :- SUM(total<PROD_TYPE>PROD_TYPE)

4 column :- (Sum({<Region={'=len(Region)>0'}>}PROD_TYPE)*sum(total<PROD_TYPE,Region>{<Region= {'=len(Region)<1'}>} PROD_TYPE))

5 column :-Column(1)*Column(2)/column(3)

Re: null values

udit_k — Thu, 13 Jul 2023 12:15:52 GMT

please find your corrected expression as below :-

4 column :- (Sum({<Region={'=len(Region)>0'}>}PROD_TYPE)*sum(total<PROD_TYPE,Region>{<Region= {'=len(Region)<1'}>} PROD_TYPE))/sum(total<Region>PROD_TYPE)

As per below expression , you should have numeric field for calculation. here count is numeric field.

If you want to use PROD_TYPE for calculation field , you could use count(PROD_TYPE ) instead of sum(PROD_TYPE )

(Sum({<Region={"=len(Region)>0"}>}Count)*sum(total<USER,Region>{<Region={"=len(Region)<1"}>} Count))
/

sum(total<USER>Count)

Re: null values

inam — Thu, 13 Jul 2023 13:01:07 GMT

hello @udit_k

now i used this but not get value also i used count instead of sum:-

1 column :- =Count(PROD_TYPE)

2column :- =SUM(total<PROD_TYPE,Region>{<Region={"=len(Region)<1"}>}Count(PROD_TYPE))

3 column :- =sum(TOTAL<PROD_TYPE>Count(PROD_TYPE))

4 column :- =(sum({<Region={'=len(Region)>0'}>}PROD_TYPE)*sum(TOTAL<PROD_TYPE,Region>{<Region= {'=len(Region)<1'}>} PROD_TYPE))/sum(TOTAL<Region>PROD_TYPE)

5 column :-Column(1)*Column(2)/column(3)

Re: null values

udit_k — Thu, 13 Jul 2023 14:04:20 GMT

try to evaluate using count of(Prod_Type )in 4 column exp .

If not please could provide dummy data .

Re: null values

inam — Fri, 14 Jul 2023 09:23:51 GMT

hi @udit_k

I tried Multiple times but not getting any value 😕 I used (sum count )but not getting answer.

please find below my snap shot and expression.

1) =Count(PROD_TYPE)

2) =count(total<Region>{<Region={"=len(PROD_TYPE)<1"}>}Count(PROD_TYPE))

3) =sum(TOTAL<PROD_TYPE>Count(PROD_TYPE))

4) =(Count({<Region={'=len(Region)>0'}>}PROD_TYPE)*Count(TOTAL<PROD_TYPE,Region>{<Region= {'=len(Region)<1'}>}Count( PROD_TYPE)))/Count(TOTAL<Region>PROD_TYPE)

5) =Column(1)*Column(2)/column(3)

and also if i get proper data how i hide my unuesd column i want to show dashboard like this

Re: null values

udit_k — Fri, 14 Jul 2023 10:05:25 GMT

Please could you correct me that values which you are showing in pivot table is not correct to count(PROD_TYPE).

It seems to me that it will be something other than, like sum(Count) as in example posted earlier and following those calculations.

Could you correct your expression to field other than PROD_TYPE , which is giving you correct values in pivot table.

Re: null values

shree083 — Fri, 14 Jul 2023 11:52:38 GMT

Check constraint - so are you inserting allowable values? Look at the table definition to see?