If expression in Qlikview

Question

My expression:

IF(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(FIELD1),FIELD1B,if(Min(MONTHYEAR) > Min(MONTHYEAR2) and len(FIELD1)>0,FIELD1)) as Value

I need this: 1.-if(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(FIELD1) output FIELD1B 2.-if(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnotnull(FIELD1) output FIELD1

What I am doing wrong? Thanks!

score 0 · Accepted Answer · answered Jan 19 '16 at 12:24

I imagine you are getting a "Generic Script Error".

If you are using an aggregation in your script lines you need to aggregate everything or add it as a field for the script to group by.

IF(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(FIELD1), FIELD1B, 
IF(Min(MONTHYEAR) > Min(MONTHYEAR2) and len(FIELD1)>0,FIELD1)) as Value
from XYZ
group by FIELD1,FIELD1B

or

IF(MONTHYEAR > MONTHYEAR2 and isnull(FIELD1), FIELD1B, 
IF(MONTHYEAR > MONTHYEAR2 and len(FIELD1)>0,FIELD1)) as Value
from XYZ

or

IF(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(only(FIELD1)), only(FIELD1B), 
IF(Min(MONTHYEAR) > Min(MONTHYEAR2) and len(only(FIELD1))>0, only(FIELD1))) as Value
from XYZ
group by 1

Not sure how the 'group by' would handle nulls or what your data looks like but the theory of either aggregating or grouping all fields holds. The only() command returns the value if there is only 1 distinct value across all the grouped lines otherwise it returns null.

If expression in Qlikview

1 Answers1