Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bassofrem
New 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

Tags (1)
5 Replies
adityaakshaya
Contributor III

Re: load order by not workding

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
New Contributor

Re: load order by not workding

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
Contributor III

Re: load order by not workding

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
Valued Contributor

Re: load order by not workding

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
Honored Contributor III

Re: load order by not workding

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;