topic Re: Getting invalid Expression when using multiple IF condition in QlikView

Getting invalid Expression when using multiple IF condition

Wed, 13 May 2015 10:47:52 GMT

Hi ,

Original Code:

load [ID], if(max(isDraft)=1 and min(isDraft)=0, 2, if( max(isDraft) <2, 1, max(isDraft)) ) as docMaxPublishedVersion,

if(max(isDraft)=1 and min(isDraft)=0 , 1, 0 ) as oneDraftOnePublish

resident AdminDoc

Modified Code:

load [docID], if(max(isDraft)=1 and min(isDraft)=0, 2, if( max(isDraft) <2,1,if(DocIsDeleted = 1 ,Version,max(isDraft))) ) as docMaxPublishedVersion,

if(max(isDraft)=1 and min(isDraft)=0 , 1, 0 ) as oneDraftOnePublish

resident AdminDoc

We have added one more condition to the Original Code( i.e DocIsDeleted ), adding this if condition in Modified Code, results in INVALID EXPRESSION.

Also tried ISNULL , got same error.

please advice.

thanks in advance

Re: Getting invalid Expression when using multiple IF condition

marcus_sommer — Wed, 13 May 2015 10:52:25 GMT

Your load is an aggregation load which needs an group by statement - have you DocIsDeleted added to them?

- Marcus

Re: Getting invalid Expression when using multiple IF condition

manojkulkarni — Wed, 13 May 2015 10:54:28 GMT

could you post your application..

Re: Getting invalid Expression when using multiple IF condition

jonathandienst — Wed, 13 May 2015 10:54:33 GMT

Does the LOAD have a Group By clause? Check that all of the non-aggregated fields (eg docID) are in the group by, and none of the aggregated fields are.

i suspect that DocIsdeleted is not in the GROUP BY.

HTH

Jonathan

Re: Getting invalid Expression when using multiple IF condition

Wed, 13 May 2015 11:03:42 GMT

thanks,

load [docID], if(max(isDraft)=1 and min(isDraft)=0, 2, if( max(isDraft) <2,1,if(DocIsDeleted = 1 ,Version,max(isDraft))) ) as docMaxPublishedVersion,

if(max(isDraft)=1 and min(isDraft)=0 , 1, 0 ) as oneDraftOnePublish

resident AdminDoc

group by [docID].

this is what the code is, so i need to add the DocIsDeleted to the group by so it looks like

group by [docID],DocIsDeleted ?

tried the above did not work.

correct me ,if am wrong

Re: Getting invalid Expression when using multiple IF condition

Wed, 13 May 2015 11:05:16 GMT

thanks,

i suspect that DocIsdeleted is not in the GROUP BY.?

you are right, but after adding also i see the same error

Re: Getting invalid Expression when using multiple IF condition

Wed, 13 May 2015 11:16:28 GMT

Thanks all,

it worked ,as i added another field Version into the Group By.

i would also like to know what is aggregation load. please explain.

thanks again.

Re: Getting invalid Expression when using multiple IF condition

marcus_sommer — Wed, 13 May 2015 12:39:01 GMT

Each time if you used expressions like max/min/avg/sum/concat you made an aggregation load which will be grouped on these fields which are not included in the above mentioned expressions and which will be need to call explicit in these group by statement.

- Marcus