Invalid Expression - using not null() and concat(distinct( in the data load editor

[1600eads]

I am having a hard time understanding why this specific set of code will not execute in the data load editor. 

if(NOT isnull(BUSINESS_SALE), BUSINESS_SALE,
if(Concat(DISTINCT POTENTIAL_LEAD_SALE, ', ', POTENTIAL_LEAD_SALE_DATE) = '', Null(),
Concat(DISTINCT POTENTIAL_LEAD_SALE,', ', POTENTIAL_LEAD_SALE_DATE))) as POTENTIAL_BUSINESS_SALE

The above code will work on the front-end (without the alias) but it doesn't seem to work when adding it as part of a left join. Also doesn't work when trying to conduct a NoConcatenate back to my main table in the data load editor.

Oddly enough, when I run just the code below

if(Concat(DISTINCT POTENTIAL_LEAD_SALE, ', ', POTENTIAL_LEAD_SALE_DATE) = '', Null(),
Concat(DISTINCT POTENTIAL_LEAD_SALE,', ', POTENTIAL_LEAD_SALE_DATE))) as POTENTIAL_BUSINESS_SALE

it works perfectly fine. Once I add in the 1st set of the if statement condition (the isnull() part) it throws me this error.

Any feedback would be greatly appreciated from the community. Thanks!

[rwunderlich · Accepted solution]

When using a Group By, the load fields must either be one of the Group fields or use an aggregation function. Your use of IsNull(BUSINESS_SALE) violates this rule. 

(Just an aside here, I've always wondered why Qlik reports this problem as "Invalid expression" instead of stating the rule in the message as SQL processors do).

-Rob

[1600eads]

That makes sense. I wish the errors would show up as such to alleviate any confusion. Thanks Rob.