Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello friend
The below script is not ordering the table according to agreement_id and collateral_id.
The purpose is to calculate the "agreement_amortization_schedule_calc
The calculated result is not correct, and it seems the reasons is that the Order By is not working.
What am i missing here ?
Collaterals:
LOAD
num#(collateral_id) as collateral_id,
num#(collateral_reference) as collateral_reference,
initial_amount as collateral_initial_amount,
original_loan_amount_calc,
num#(agreement_id) as agreement_id
FROM [lib://QVD First Layer (bsec-sa_bfrem_bsec)/Collaterals.qvd](qvd);
Left Join (Collaterals)
data_temp:
Load
agreement_id,
collateral_id,
if(agreement_id<>peek(agreement_id),original_loan_amount_calc,Peek(agreement_amortization_schedcule_calc))
-collateral_initial_amount as agreement_amortization_schedcule_calc
Resident Collaterals
Order by agreement_id, collateral_id asc;
Thanks
Hi Bassam,
I will suggest, first do the left join and then do the order by.
In your script, you are ordering the data_temp and joining it with Collaterals table.
Rather, I would like to suggest like below
Collaterals_Temp:
LOAD
num#(collateral_id) as collateral_id,
num#(collateral_reference) as collateral_reference,
initial_amount as collateral_initial_amount,
original_loan_amount_calc,
num#(agreement_id) as agreement_id
FROM [lib://QVD First Layer (bsec-sa_bfrem_bsec)/Collaterals.qvd](qvd);
Left Join (Collaterals_Temp)
Load
agreement_id,
collateral_id,
if(agreement_id<>peek(agreement_id),original_loan_amount_calc,Peek(agreement_amortization_schedcule_calc))
-collateral_initial_amount as agreement_amortization_schedcule_calc
Resident Collaterals
NoConcatenate
Collaterals
Load *
resident Collaterals_Temp order by agreement_id, collateral_id asc;
Drop table Collaterals_Temp ;
Regards,
Akshaya
PS - If you find response helpful or correct, Please mark it.
Hello Akshaya
The calculation formula for agreement_amortization_schedule_calc has to be made after the ordering command (otherwise the result would not be correct)
What would you suggest in this case ?
Best regards
Bassam
Hi Bassam,
I would like to suggest the below script. This will work in your case
Collaterals_Temp:
LOAD
num#(collateral_id) as collateral_id,
num#(collateral_reference) as collateral_reference,
initial_amount as collateral_initial_amount,
original_loan_amount_calc,
num#(agreement_id) as agreement_id
FROM [lib://QVD First Layer (bsec-sa_bfrem_bsec)/Collaterals.qvd](qvd) order by agreement_id, collateral_id asc;
NoConcatenate
Collaterals:
Load *
, if(agreement_id<>peek(agreement_id),original_loan_amount_calc,Peek(agreement_amortization_schedcule_calc))
-collateral_initial_amount as agreement_amortization_schedcule_calc
resident Collaterals_Temp order by agreement_id,collateral_id asc;
Drop table Collaterals_Temp;
Do as Akshaya says, just move agreement field to last table;
NoConcatenate
Collaterals
Load *,
f(agreement_id<>peek(agreement_id),original_loan_amount_calc,Peek(agreement_amortization_schedcule_calc))
-collateral_initial_amount as agreement_amortization_schedcule_calc
resident Collaterals_Temp order by agreement_id, collateral_id asc
I think it may be that your calculation have an issue. Please load the table with order by clause alone to see if the ordering works as expected.
Can you see if both the id's are actually numbers?
Collaterals:
LOAD
num#(collateral_id) as collateral_id,
num#(collateral_reference) as collateral_reference,
initial_amount as collateral_initial_amount,
original_loan_amount_calc,
num#(agreement_id) as agreement_id
FROM [lib://QVD First Layer (bsec-sa_bfrem_bsec)/Collaterals.qvd](qvd);
//Left Join (Collaterals)
data_temp:
Noconcatenate Load
agreement_id,
collateral_id,
// if(agreement_id<>peek(agreement_id),original_loan_amount_calc,Peek(agreement_amortization_schedcule_calc))
-collateral_initial_amount as agreement_amortization_schedcule_calc
Resident Collaterals
Order by agreement_id, collateral_id asc;