Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
43918084
Creator II
Creator II

Calculated field based on another calculated field

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]

1 Solution

Accepted Solutions
mfarsln
Creator II
Creator II

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

 

 

View solution in original post

2 Replies
mfarsln
Creator II
Creator II

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

 

 

43918084
Creator II
Creator II
Author

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.