Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
43918084
Creator II
Creator II

Create calculation after joining tables in script

I have outer join 2 tables one as current period and one as last period.  After joining, I need to do calculation using the joined data.  May I know how I do it in script?

I have invalid expression in the below script:

[FTP]:
LOAD
Period,
"Ip Shrt Name Ith",
"Arr Lcl Num Ath",
"Global Sic Code" as t0_SicCode,
"Month, Day, Year of Glbl Trade Date" as t0_TradeDate,
"Month, Day, Year of Maturity Date" as t0_Maturity,
"Credit Grade Code" as t0_CRR,
amt as t0_Amt
FROM [lib://Loan/QS test1.xlsx]
(ooxml, embedded labels, table is Sheet1);


Outer join

LOAD
"Arr Lcl Num Ath",
Period as t1_period,
"Ip Shrt Name Ith",
"Global Sic Code" as t1_SicCode,
"Month, Day, Year of Glbl Trade Date" as t1_TradeDate,
"Month, Day, Year of Maturity Date" as T1_maturity,
"Credit Grade Code" as t1_CRR,
amt as t1_Amt

FROM [lib://Loan/QS test2.xlsx]
(ooxml, embedded labels, table is Sheet1);

 


[New]:
Load*,

sum(t1_Amt) - sum(t0_Amt) as Var

Resident [FTP] group by "Arr Lcl Num Ath";


Drop Table [FTP];

Labels (1)
6 Replies
morgankejerhag
Partner
Partner

Depending on how you want to use this it might be better to concatenate the tables, then make a new load reading resident data in sorted order and use peek() to retrieve the previous period.

Morgan Kejerhag
Senior Business Intelligence Consultant
Drake Analytics
http://www.drakeanalytics.se

43918084
Creator II
Creator II
Author

Thank you very much for your advice.

if I use concatenate, the same [Arr Lcl Num Ath] but in different period will not be in the same line1.PNG

I need the same [Arr Lcl Num Ath] in different period to be in the same line for comparison like below

1.PNG

I have tried below revised script.  But I get error "field 't1_Amt' not found".  It seems the new table does not recognize the defined field under the resident table highlighted in RED.

Hope I can get more guidance.

[1]:
LOAD
Period,
"Ip Shrt Name Ith",
"Arr Lcl Num Ath",
"Global Sic Code" as t0_SicCode,
"Month, Day, Year of Glbl Trade Date" as t0_TradeDate,
"Month, Day, Year of Maturity Date" as t0_Maturity,
"Credit Grade Code" as t0_CRR,
amt as t0_Amt
FROM [lib://Loan/QS test1.xlsx]
(ooxml, embedded labels, table is Sheet1);


//Outer join
//Keep

LOAD
"Arr Lcl Num Ath",
Period as t1_period,
"Ip Shrt Name Ith",
"Global Sic Code" as t1_SicCode,
"Month, Day, Year of Glbl Trade Date" as t1_TradeDate,
"Month, Day, Year of Maturity Date" as T1_maturity,
"Credit Grade Code" as t1_CRR,
amt as t1_Amt

FROM [lib://Loan/QS test2.xlsx]
(ooxml, embedded labels, table is Sheet1);


NoConcatenate

[New]:
Load*,

sum( t1_Amt-t0_Amt) as Var

Resident [1] Order by "Arr Lcl Num Ath";


Drop Table [1];

Anil_Babu_Samineni

I will try this way


[1]:
LOAD
Period,
"Ip Shrt Name Ith",
"Arr Lcl Num Ath",
"Global Sic Code" as t0_SicCode,
"Month, Day, Year of Glbl Trade Date" as t0_TradeDate,
"Month, Day, Year of Maturity Date" as t0_Maturity,
"Credit Grade Code" as t0_CRR,
amt as t0_Amt
FROM [lib://Loan/QS test1.xlsx]
(ooxml, embedded labels, table is Sheet1);

Join

LOAD
"Arr Lcl Num Ath",
Period as t1_period,
"Ip Shrt Name Ith",
"Global Sic Code" as t1_SicCode,
"Month, Day, Year of Glbl Trade Date" as t1_TradeDate,
"Month, Day, Year of Maturity Date" as T1_maturity,
"Credit Grade Code" as t1_CRR,
amt as t1_Amt

FROM [lib://Loan/QS test2.xlsx]
(ooxml, embedded labels, table is Sheet1);


Left Join (1)

[New]:
Load "Arr Lcl Num Ath",

sum( t1_Amt-t0_Amt) as Var

Resident [1] Group By "Arr Lcl Num Ath" Order by "Arr Lcl Num Ath";


 

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
43918084
Creator II
Creator II
Author

Thank you so much.  Unfortunately, I still get the same error, "Field 't1_amt' not found.

Anil_Babu_Samineni

Can you please share sample data set to check where it is failing?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
43918084
Creator II
Creator II
Author

Hi Mr Samineni

Please also find the qvf attached for your reference.  Thank you very much again.