Getting invalid Expression when using multiple IF condition

[#1]

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

[marcus_sommer · Accepted solution]

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

- Marcus

[manojkulkarni]

could you post your application..

[jonathandienst]

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

[#5]

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

[#6]

thanks,

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

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

[#7]

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.

[marcus_sommer]

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