The arrangement of the loaded file affect the outcome

[tan_chungkam]

I have 4 excel files which loaded as below:

I created 2 new columns by scripting as shown below:

However if I loaded the excel file without following the months, the result will be different.

For example: I load Feb file then only Jan file: the outcome will be like below:

Is there any scripting could fix this matter.

I would like the script could define the order no. base on month instead of which files load in first.

[tan_chungkam]

Hi ishanbhatt‌,

Just wonder what if my files name are not Jan, Feb and Mar.

I guess the above script will not be working on it , right? Thanks.

Chung.

[thannila]

Directory [lib://f];

For each ExcelFile in filelist ('*.xlsx')

Main:

Load *

From $(ExcelFile)

(ooxml, embedded labels, table is Sheet1);

Next ExcelFile;

left join

Table1:

Load * Inline

[

Month,Monthid

January,1

February,2

March,3

April,4

May,5

June,6

July,7

August,8

September,9

October,10

November,11

December,12

];

NoConcatenate

Table2:

load * Resident Main

order by Monthid;

drop table Main;

[tan_chungkam]

Hi,

Thanks for the reply.

I would appreciate if you could explain the above script

[thannila]

Hi,

In this script,i have loaded all the excel files.Our requirement is get the result based on month.

so i have created a Master table for Month(Inline load).using left join ,the Month id is added to that sheet.Then, i have taken resident load to order the table based on Month using Month id. The same result is produced.