Error in loading

[KaisGarci]

Hello everyone, 

This is my script: 

for Each vFile in FileList('C:\REAL_*.xlsx')

Files:
LOAD subfield('$(vFile)','\',-1) as FileName,
date(date#(mid('$(vFile)',index('$(vFile)','_',-1)+1,4),'DDMM')) as Month,
'$(vFile)' as Path
AutoGenerate 1;

next

Files_needed:
NoConcatenate
first 2 LOAD *
Resident Files
Order by Month desc;

DROP Table Files;

Data:
LOAD * Inline [
Temp ];

for i=1 to FieldValueCount('Month')

let vFilePath = FieldValue('Path',$(i));

Concatenate(Data)
LOAD *,
FileBaseName() as FileName
FROM
[$(vFilePath)]
(ooxml, embedded labels, table is Sheet1); // you can change this highlighted part as you may have different way of loading the excel files

NEXT

DROP Table Files_needed;

DROP Field Temp;

It is supposed to load the two latest files, I have files named Real_DDMMYYYY. But it is considering that the file Real_31082020 is latest that Real_11092020 which is not true. 
Can you please help me with that, I have a deadline in two days. 
Thank you

[rubenmarin]

Hi, I think you should change

date(date#(mid('$(vFile)',index('$(vFile)','_',-1)+1,4),'DDMM')) as Month

to

date(date#(mid('_31082020',index('_31082020','_',-1)+1,8),'DDMMYYYY')) as FileDate

 And use FileDate instead of Month in the next script sentences, it you only use Month you will get an isue when january-2021 comes.

But still I don't know why it sets august higher than september in your code.

[KaisGarci]

Thanks for your help Mr @rubenmarin. But I want it to load automatically the two latest file. In your example, it will not do that. 

[Kushal_Chawda]

@KaisGarci  try below

for Each vFile in FileList('C:\REAL_*.xlsx')

Files:
LOAD subfield('$(vFile)','\',-1) as FileName,
date(date#(trim(mid('$(vFile)',index('$(vFile)','_',-1)+1)),'DDMMYYYY')) as Date,
'$(vFile)' as Path
AutoGenerate 1;

next

Files_needed:
NoConcatenate
first 2 LOAD *
Resident Files
Order by Date desc;

DROP Table Files;

Data:
LOAD * Inline [
Temp ];

for i=1 to FieldValueCount('Date')

let vFilePath = FieldValue('Path',$(i));

Concatenate(Data)
LOAD *,
FileBaseName() as FileName
FROM
[$(vFilePath)]
(ooxml, embedded labels, table is Sheet1); // you can change this highlighted part as you may have different way of loading the excel files

NEXT

DROP Table Files_needed;

DROP Field Temp;

[KaisGarci]

It gave me nothing Mr. @Kushal_Chawda . 
I made some modifications, it is loading correctly but the variable MONTH as seen here 

for i=1 to FieldValueCount('Month')

let vFilePath = FieldValue('Path',$(i));

let vMonth = FieldValue('Month',$(i));

Concatenate(Data)
LOAD *,
FileBaseName() as FileName,

'$(vMonth)' as MONTH
FROM
[$(vFilePath)]
(ooxml, embedded labels, table is Sheet1); // you can change this highlighted part as you may have different way of loading the excel files

is not working correctly. When I do max (MONTH) it gives me either 31/08/2020 instead of 09/11/2020 or a number 44086

[KaisGarci]

I think that the problem is with the variable MONTH, but when I have 31082020 and 10072020 it gives me that 31082020 but in the case of 31082020 and 10092020 it gives me also 31082020 in the max(MONTH)

[marcus_sommer · Accepted solution]

I suggest to consider seriously to change the filename-pattern from DDMMYYYY to YYYYMMDD which would avoid any measures to transform it during the load-statements. This may sound a bit ugly but from a practicable point of view it's often the fastest way - I did something similar already multiple times, ok. not with daily data else yearmonth and to change 2 - 5 years needs just 2 - 3 minutes.

With it you will keep your script much more readable and also get a proper sorting within the explorer - if the creation-date of the files is continuous you may sort for it but then you could use filetime() to get the latest files instead of evaluating the filenames.

Nevertheless if you want to keep by your origin approach you need to transform the date within the filename to the above mentioned structure of YYYYMMDD by ensuring that these aren't dates (are always only a formatting for the underlying number - on which functions like max() would be performed) else pure numbers.

- Marcus

[KaisGarci]

Hello Mr. @marcus_sommer . It is loading perfectly, but the issue is with the variable MONTH  (as you can see in this script) 

let vFilePath = FieldValue('Path',$(i));

let vMonth = FieldValue('Month',$(i));

Concatenate(Data)
LOAD *,
FileBaseName() as FileName,

'$(vMonth)' as MONTH

It gives that 08 August is bigger than 11 September

[Kushal_Chawda]

@KaisGarci  what is the value in  vMonth variable?

[KaisGarci]

It is the date variable whic is based on the two files, so it gives me to values of the latest dates. But it tells me that 31 august is latest than 11 september, when I tried to change the name to Real_10082020 (10<11) it gives me that 11 september is latest than 31 august in this case.