Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]
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];
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];
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.