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 · Accepted solution]

Hi @My_Rebecca ,

Try this

Set vPath = 'lib://FLD_DI_LOG_Reporting/Control Tower DC Report/KPI Dashboard/DC TJ/Inventory';

Let vMaxDate = 0;

For Each File in FileList(vPath &'/DC TJ Inventory_*.xlsx')

Let vDate = Date#(RIGHT(File,Len(File) - 6),'YYYYMMDD');

If vDate > vMaxDate Then

Let vMaxDate = vDate;

Let vMaxFile = File;

End If

Next

NMap1:
Mapping Load
xxxxxxxxxxxx
FROM [$(vMaxFile)]
(ooxml, embedded labels, table is Sheet1);

Inventory:
Mapping Load
xxxxxxxxxxxx
FROM [$(vMaxFile)]
(ooxml, embedded labels, table is Profit_Center);

Load
xxxxxxxxxxxx
FROM [$(vMaxFile)]
(ooxml, embedded labels, table is Sheet1)

[My_Rebecca]

Dear @sandeep-singh ,

I could not understand why "Let vMaxDate = 0"?

The max date does not equal 0......

[My_Rebecca]

Dear @sandeep-singh 

I think the 2 maps do not need to change, only the last load part needs correction. Currently your scrip does not work. I could not understand your logic.

[sandeep-singh]

To initialize the variable we have assigned the value 0. We are taking a 'vDate' variable and comparing with 'vMaxdate'. If the 'vMaxdate' variable is less than 'vDate' variable, then it will assign 'vDate' to 'vMax' date. Next time the 'vMaxdate' variable will be the current date and goes on.

I checked on my system and works well. Could you share me your script for validation?

[My_Rebecca]

Dear @sandeep-singh ,

What about this meaning?  Let vMaxFile = File;

Could you show me your script? Let me learn from yours.  Mine is too long to copy all, I think.

[sandeep-singh]

The variable "vMaxFile" is to store the path for latest file which we can use to load the file. You can find this variable in the load file statement.

I have created 3 dummy files

Here is my script, I have added trace to let you know the Data getting stored in the variable vMaxDate and vMaxFile

This the output log

[My_Rebecca]

Dear @sandeep-singh , I believe it's very very close to correct result, loading is OK, but is not the max date, but the Min date.

What's wrong8 with it?

[sandeep-singh]

okay, would you mind changing the from statement and check

instead of 

FROM [$(vMaxFile)]
(ooxml, embedded labels, table is Sheet1)

use

FROM [$(File)]
(ooxml, embedded labels, table is Sheet1)

[My_Rebecca]

Dear @sandeep-singh , thanks for your support. It works. But I still have question: why loading result shows 20230319, but the data comes from 20230326?