Calculated Dimension with multiple isnull()

[henrybergemann]

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

[OmarBenSalem]

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)

[henrybergemann]

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

Your Example wont work as a Calculated Dimension right?

[Kushal_Chawda · Accepted solution]

@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