load order by not workding

[Anonymous]

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

[adityaakshaya]

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.

[Anonymous]

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]

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]

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]

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;