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
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
bassofrem
Contributor
Contributor

load order by not workding

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

5 Replies
adityaakshaya
Creator III
Creator III

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.

bassofrem
Contributor
Contributor
Author

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

adityaakshaya
Creator III
Creator III

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;

dwforest
Specialist II
Specialist II

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

sasiparupudi1
Master III
Master III

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;