How to load the data of latest date, then rename the sheet with latest data?

[My_Rebecca]

How to load the data of latest date, then rename the sheet with latest data?

Datasource:

currently I need the data of "DC TJ Inventory_20230319",

Load

******

FROM [DC TJ Inventory_*.XLSX]
(ooxml, embedded labels, table is Sheet1)
Where max(TextBetween(FileName(),'_','.') )

But this does not work. How to script this?

Then I need to rename the sheet like this, how to realize it? Many thanks.

[MayilVahanan]

Hi

Try like below

Table1:

Load Distinct

TextBetween(FileName(),'_','.')  as FileNameDate,

FileName() as FileName

FROM [DC TJ Inventory_*.XLSX]
(ooxml, embedded labels, table is Sheet1);

Inner join(Table1) 

Load Max(FileNameDate) as FileNameDate resident Table1;

let vMaxFileName = Peek('FileNameDate');

Load * from [DC TJ Inventory_$(vMaxFileName).XLSX]
(ooxml, embedded labels, table is Sheet1);

Use "FileName" in sheetName, it will display dynamic name.

Hope it helps

[My_Rebecca]

dear,

It yet does not work. Please help to correct it, thanks.

[MayilVahanan]

No no,

Actually, you need to declare after inventory table. 
And instead of DC TJ Inventory*.xlsx, you need to replace with below to load only the latest data

DC TJ Inventory_$(vMaxFileName).XLSX 

[My_Rebecca]

sorry, I'm a beginner, it's a little hard for me to understand.

What does it mean "you need to declare after inventory table"? I change as you suggested, but not succeed.

Load

******

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

Table1:
Load Distinct
TextBetween(FileName(),'_','.') as "FileNameDate",
FileName() as "FileName"
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/Inventory/DC TJ Inventory*.XLSX]
(ooxml, embedded labels, table is Sheet1);
Inner join(Table1)
Load Max("FileNameDate") as "FileNameDate" resident Table1;
let vMaxFileName = Peek('FileNameDate');
Load * from [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/Inventory/DC TJ Inventory$(vMaxFileName).XLSX]
(ooxml, embedded labels, table is Sheet1);

[MayilVahanan]

Can you post your entire script here

[My_Rebecca]

Dear, please support to correct this script, many thanks.

INMap1:
Mapping Load
"Sales Order"&'-'&"Sales order item",
"Posting Date"
FROM xxxxxxxxxxx;

Inventory:
Mapping Load
"Profit Center",
"BU"
FROM xxxxxxxxxxx;

Load*,
if("Posting Date"='','',Today()-"Posting Date") as "Inventory Time (day)";

Load

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

ApplyMap(xxxxxxxxxxxxxxxxxxxxx)

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

Table1:
Load Distinct
TextBetween(FileBaseName(),'_','.') as FileNameDate,
FileName() as FileName
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/Inventory/DC TJ Inventory_*.XLSX]
(ooxml, embedded labels, table is Sheet1);
Inner Join (Table1)
Load Max(FileNameDate) as FileNameDate Resident Table1;
let vMaxFileName=Peek('FileNameDate');
Load * FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/Inventory/DC TJ Inventory_*.XLSX]
(ooxml, embedded labels, table is Sheet1);

[My_Rebecca]

Dear @MayilVahanan  Could you please support me on this issue? Thanks.

[MayilVahanan]

Hi,

Try like below, hope it will helps to load only the latest file info

INMap1:
Mapping Load
"Sales Order"&'-'&"Sales order item",
"Posting Date"
FROM xxxxxxxxxxx;

Inventory:
Mapping Load
"Profit Center",
"BU"
FROM xxxxxxxxxxx;

Table1:
Load Distinct
TextBetween(FileBaseName(),'_','.') as FileNameDate,
FileName() as FileName
FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/Inventory/DC TJ Inventory_*.XLSX]
(ooxml, embedded labels, table is Sheet1);

Inner Join (Table1)
Load Max(FileNameDate) as FileNameDate Resident Table1;
let vMaxFileName=Peek('FileNameDate');

Drop table Table1;

Load*,
if("Posting Date"='','',Today()-"Posting Date") as "Inventory Time (day)";

Load

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

ApplyMap(xxxxxxxxxxxxxxxxxxxxx)

FROM [lib://FLD_DI_LOG_Reporting/Control Tower DC Report/DC TJ/Inventory/DC TJ Inventory_$(vMaxFileName).XLSX]
(ooxml, embedded labels, table is Sheet1);

[My_Rebecca]

dear @MayilVahanan , thanks for your patience. Please take a look at what I input here per your script.

I feel very close to success, yet last step perhaps. How to make the final correction?