How to load the file of latest date?

[My_Rebecca]

This is the second time I post for this topic. Hope this time I could get the answer.

How to load the data of latest date?

Datasource:

I need the data of "DC TJ Inventory_20230319". How to correct following script?

INMap1:
Mapping Load
xxxxxxxxxxxx
FROM [xxxxxxxxxxxxxxxxxxx.XLSX]
(ooxml, embedded labels, table is Sheet1);

Inventory:
Mapping Load
xxxxxxxxxxxx
FROM [xxxxxxxxxxxxxxxxxxxx.xlsx]
(ooxml, embedded labels, table is Profit_Center);

let vMaxFileNameDate = date(max(TextBetween(FileName([lib://FLD_DI_LOG_Reporting/Control Tower DC Report/KPI Dashboard/DC TJ/Inventory/DC TJ Inventory_*.XLSX]),'_','.')),'YYYYMMDD');

Load
xxxxxxxxxxxx
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/KPI Dashboard/DC TJ/Inventory/DC TJ Inventory_*.XLSX]
(ooxml, embedded labels, table is Sheet1)

Where Date(TextBetween(FileName(),'_','.'),'YYYYMMDD')>='$(vMaxFileNameDate)';

[sandeep-singh]

You have to change your trace function

from

Trace '$(vMaxFile)';

to

Trace '$(File)';

[My_Rebecca]

Dear @sandeep-singh , thanks for your reply. 

But in this scrip, there are some mapping before precedent loading. These mapping tables need load latest data as well, so I need to set the variable the same way with different name, such as vPath1, vMaxDate1, vMaxField1, File1, is it right?

[sandeep-singh]

We are now having a latest data in the app. You can use the resident load for the mapping table.

[My_Rebecca]

The mapping table data source is different, so I think vPath1, vMaxDate1, vMaxField1, File1 perhaps need to be created before mapping table.

[My_Rebecca]

Dear @sandeep-singh , how to use resident load in case of mapping table of latest date source? I tried following script before mapping table, but fail. Please help to correct it, thanks.

[sandeep-singh]

You can use the same code for the new table. Just change the path for the new table.

[My_Rebecca]

Dear @sandeep-singh , please have a look at my screen shot, as I have already changed the new path and rename the new variables, such as vPath1, vMaxDate1, vMaxField1, File1 for the mapping table, yet not succeed. How to correct it? I hope I could give you another "Accept as Solution" in one post, thanks.

[sandeep-singh · Accepted solution]

Hi @My_Rebecca, Please so use the same code for the new table and just change the highlighted area with new path. No need to create a new variable.

Set vPath = 'lib://DataFiles/';

Let vMaxDate = 0;

For Each File in FileList(vPath &'HourlyWages-*.xlsx')
Let vDate = Date#(SubField(left(File,Len(File) - 5),'-',2),'YYYYMMDD');
If vDate > vMaxDate Then
Let vMaxDate = vDate;
Let vMaxFile = File;
End If

Next

Trace '$(vMaxDate)';
Trace '$(File)';

[My_Rebecca]

Dear @sandeep-singh , thanks again for your patient answer in this post. I try according to your idea and succeed.