Create calculation after joining tables in script

[43918084]

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

[morgankejerhag]

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]

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 line

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

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

---

[43918084]

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?

[43918084]

Hi Mr Samineni

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