Sort a table when loaded from excel

[janus2021]

Hello.
Have a problem that is probably not that difficult for those who know how to do 🙂
I am loading an excel file that has 5 columns. While loading I want to sort the column: [% INDIVID_PERIOD_KEY].
so after sorting it should look like the result in step 4.

i include the app and excel-file.

/*
//data in from excel-file: EmpPayment
Payment_ID,Empl_ID,Name,Payment,TaskGiven,Period,
195003042222 1 Person 1 12100 Cash paymen 202101
195003042222 1 Person 1 12950 Cash paymen 202102
195003042244 3 Person 3 14222 Extra Payment 202102
195003042255 4 Person 4 15985 Car Payment 202103
195003042222 1 Person 1 10125 living payment 202104
195003042233 2 Person 2 14500 Cash paymen 202104
*/

//from App: EmpPay_sorted
//section: vVariable, this will be a dynamic input but now static.
set v_period= '202101','202102','202103','202104';

//Section: excel
//Step 1:
[EmpPayment]:
LOAD *
Inline
[Payment_ID, Empl_ID,%INDIVID_PERIOD_KEY,Empl_ID_File, Name, Payment, TaskGiven, Period_FromFile
];

//autoconcatinering
//Step 2: load from Excel
LOAD
Payment_ID,
Empl_ID,
"Payment_ID"&'|'&Period as %INDIVID_PERIOD_KEY,
NUM(Payment_ID) as Empl_ID_File,
Name,
Payment,
TaskGiven,
Text(Period) as Period_FromFile
FROM [lib://Excel/EmpPayment.xlsx]
(ooxml, embedded labels, table is [EmpPay]);

//Step 3: Wanted sorted table
[PaymentTable]:
NoConcatenate
LOAD
Payment_ID,
[Empl_ID],
[%INDIVID_PERIOD_KEY],
[Empl_ID_File],
Name,
Payment,
TaskGiven,
[Period_FromFile]
Resident [EmpPayment]
where Match([Period_FromFile],$(v_period));

Drop Table EmpPayment;

//Step 4: result.
result in PaymentTable:
column: %INDIVID_PERIOD_KEY ,sorted like:
195003042222|202101
195003042222|202102
195003042244|202102
195003042255|202103
195003042222|202104
195003042233|202104

Wanted sort order in PaymentTable:
column: %INDIVID_PERIOD_KEY
195003042222|202101
195003042222|202102
195003042222|202104
195003042233|202104
195003042244|202102
195003042255|202103

So i want to sort in the' Load' step and not in a sheet.

BR

Janus.

[maxgro · Accepted solution]

Maybe adding an order by in step 3

//Step 3: Wanted sorted table
[PaymentTable]:
NoConcatenate
LOAD
Payment_ID,
[Empl_ID],
[%INDIVID_PERIOD_KEY],
[Empl_ID_File],
Name,
Payment,
TaskGiven,
[Period_FromFile]
Resident [EmpPayment]
where Match([Period_FromFile],$(v_period))
ORDER BY Payment_ID, Period_FromFile
;