topic "Invalid expression" error when trying to use If statement with Resident load. in App Development

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

eoinboylan — Sat, 16 Nov 2024 07:53:05 GMT

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

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

sunny_talwar — Mon, 30 Jul 2018 17:57:02 GMT

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];

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

eoinboylan — Tue, 31 Jul 2018 08:45:54 GMT

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.

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

sunny_talwar — Tue, 31 Jul 2018 11:11:34 GMT

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

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

eoinboylan — Tue, 31 Jul 2018 11:21:05 GMT

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!

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

sunny_talwar — Tue, 31 Jul 2018 11:23:22 GMT

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

Best,

Sunny