topic Calculated field based on another calculated field in App Development

Calculated field based on another calculated field

43918084 — Mon, 20 Dec 2021 21:32:42 GMT

I need to create a calculated field depending on the result of another calculated field. I thought I have to build a new table for that dependent cacluated field. But I get error that the preceding calculated field cannot be found.

Hope I can get some guidance. The script is as below

[LYCM]:

Load Reportdate, TradeID, MatDate, TradeDate, Notional

From [lib://Lastyear.xlsx]

(ooxml, embedded table, Table is Sheet1);

Concatenate

Load Reportdate, TradeID, Matdate, TradeDate, Notional

From [lib://Currentmonth.xlsx]

(ooxml, embedded table, Table is Sheet2);

Left Join Load TradID, Matdate, TradeDate,

Count(TradeID) as occur

Resident [LYCM] Group by TradeID, MatDate, TradeDate;

[NewTable]:

Load*

If(occur =1 and Reportdate='20191231' and MatDate > reportdate, 'Terminated',

if(ocurr=1 and Reportdate<>'20191231','new',

if(occur=1 and Reportdate='20191231','matured',

if(occur>1,'exist', chk')))) as Status

Resident [LYCM];

Drop Table [LYCM]

Re: Calculated field based on another calculated field

mfarsln — Tue, 03 Mar 2020 14:06:55 GMT

Hi,

There are some syntax errors. Field names are case sensitive. So you should use Reportdate as field name. Some semicolons were missing too. Can you try with below script?

[LYCM]: Load Reportdate, TradeID, MatDate, TradeDate, Notional From [lib://Lastyear.xlsx] (ooxml, embedded table, Table is Sheet1); Concatenate Load Reportdate, TradeID, Matdate, TradeDate, Notional From [lib://Currentmonth.xlsx] (ooxml, embedded table, Table is Sheet2); Left Join Load TradID, Matdate, TradeDate, Count(TradeID) as occur Resident [LYCM] Group by TradeID, MatDate, TradeDate; NoConcatenate [NewTable]: Load* , If(occur =1 and Reportdate='20191231' and MatDate > Reportdate, 'Terminated', if(ocurr=1 and Reportdate<>'20191231','new', if(occur=1 and Reportdate='20191231','matured', if(occur>1,'exist', chk')))) as Status Resident [LYCM]; Drop Table [LYCM];

Re: Calculated field based on another calculated field

43918084 — Tue, 03 Mar 2020 14:20:45 GMT

Thank you very much for your immediate help. You guidance have solved the problem I have struggled for many days as I am new to Qlik sense with limited scripting experience.