"Invalid expression" error when trying to use If statement with Resident load.

[eoinboylan]

Hi,

I'm trying to use the following if statement (in italics) to generate the current tariff for a phone company who have a certain tariff for weekends (name begins with WEE) and also have brought in new charges in certain tariffs (but not others) recently. 

The following block of code is what's included when the Qlik Sense loader flags up an error, stating it is an Invalid Expression, but I have used something identical in another loader and it works fine. 

Does anyone have any idea why this may not be working?

     Left join (Current_Table)

     Load Relationship_ID,

     if(left([Agreement Name],3)='WEE',

     FirstSortedValue(Distinct [Tariff Name],-effective_from_date) ,

     if(len(FirstSortedValue(Distinct [Tariff Name],-applied_from_date))< 1,

     FirstSortedValue(Distinct [Tariff Name],-effective_from_date),

     FirstSortedValue(Distinct [Tariff Name],-applied_from_date))) as "Current Tariff",

     FirstSortedValue(Distinct [Tariff Rates],-applied_from_date) as "Current Tariff Rate",

     FirstSortedValue(Distinct [Tariff Rental Charge],-"Date Tariff Apply From") as "Current Rental Charge"

     Resident Temp_Table

     group by Relationship_ID;

Thanks in advance for your help!

Eoin

[sunny_talwar]

Either add [Agreement Name] to your group by

    Left join (Current_Table)

    Load Relationship_ID,

    if(left([Agreement Name],3)='WEE',

    FirstSortedValue(Distinct [Tariff Name],-effective_from_date) ,

    if(len(FirstSortedValue(Distinct [Tariff Name],-applied_from_date))< 1,

    FirstSortedValue(Distinct [Tariff Name],-effective_from_date),

    FirstSortedValue(Distinct [Tariff Name],-applied_from_date))) as "Current Tariff",

    FirstSortedValue(Distinct [Tariff Rates],-applied_from_date) as "Current Tariff Rate",

    FirstSortedValue(Distinct [Tariff Rental Charge],-"Date Tariff Apply From") as "Current Rental Charge"

    Resident Temp_Table

    group by Relationship_ID, [Agreement Name];

[eoinboylan]

Hi Sunny,

Thanks for your prompt response!  your addition to the group by has fixed the error, the expression now works, however it is causing other values loaded in the Current_Table loaded before this join to no longer load, which is strange. 

[sunny_talwar]

Would you be able to share a sample data and let us know what is the expected output based on the data provided?

[eoinboylan]

I consulted with a work colleague, it turned out he had added an Exit Script while trying to help me without my knowledge, so I was running into that and not noticing.  My own silly mistake.

The expression works perfectly since the introduction of the extra field in the group by.

Thank you so much for your help Sunny!

[sunny_talwar]

Awesome, I am glad you were able to figure this out with the help of your colleague.

Best,

Sunny