topic Re: Calculated Dimension with multiple isnull() in QlikView

Calculated Dimension with multiple isnull()

henrybergemann — Thu, 17 Sep 2020 08:29:00 GMT

Hello Community

i have a Question about this Topic:

My Calculated Dimension looks like this:

=if(len(trim(Kündigung_Bestätigt))=0
and len(trim([TR5 Update]))=0
and len(trim(MachinemoveDocno))=0
and len(trim(ActivityCodeMachine))=0
,'Offen','No')

I Would like to make a dimension when all these Field are Empty, it should give me "Offen" But the Calculated Dimension only shows me a Blank Field. (In my Example alle Fields are Empty, so it should give me "Offen"

(I also tried isNull(Field))

When i only make one Statement like

=if(len(trim(Kündigung_Bestätigt))=0,'Offen','No')

it works fine.

where is may Mistake?

Thank you in advance

Henry

Re: Calculated Dimension with multiple isnull()

OmarBenSalem — Thu, 17 Sep 2020 08:33:22 GMT

maybe try to aggregate the whole thing to a dimension?

Exp of data :

DimID , Kündigung_Bestätigt,[TR5 Update],MachinemoveDocno,ActivityCodeMachine

1,,,,

2,1,,,

3,,,,

in this case you should have 'Offen' for dimID 1 and 3..

why? beacause all values of ur 4 dimensions are nulls.. GROUP BY your DIMID !

which means, ur expression would rather be :

=aggr(if(len(trim(Kündigung_Bestätigt))=0
and len(trim([TR5 Update]))=0
and len(trim(MachinemoveDocno))=0
and len(trim(ActivityCodeMachine))=0
,'Offen','No'),DimID)

Re: Calculated Dimension with multiple isnull()

henrybergemann — Thu, 17 Sep 2020 08:48:22 GMT

Well my future Plan is of course to make this Field not a calculated Dimension .

Your Example wont work as a Calculated Dimension right?

Re: Calculated Dimension with multiple isnull()

Kushal_Chawda — Thu, 17 Sep 2020 10:14:32 GMT

@henrybergemann I would suggest to move your if condition in load script instead because in calculated dimension you may need to use aggr function to work properly . You can do something like below

LOAD .....

if(len(trim(Kündigung_Bestätigt))=0
and len(trim([TR5 Update]))=0
and len(trim(MachinemoveDocno))=0
and len(trim(ActivityCodeMachine))=0
,'Offen','No') as Flag

FROM table;

Then you can simply call Flag field in your dimension