How to load 4 months data into data load editor

[Luffy20]

Hi All,

I have 4 months of data in 4 different excels, In dashboard if we want to selct dec month only dec data should come or if we select other months , other months data should come . i have loaded by using for loop,but i am not getting correct data in dashboard. Can you guys help me.

For Each vFile in FileList('$(DataPath)/Inventory/')

TempInventory:

LOAD
*
FROM [$(vFile)]
(ooxml, embedded labels, header is 1 lines, table is Sheet1)
Where RecNo() > 1 and Len(Trim("Company Code"))>0
and MonthStart(Date(Date#(Mid(FileName(), 24, 8), 'MMM YYYY'))) >= '$(vLast36Month)';

Next;

[Vegar]

What's the issue? Are  you getting any data into your dashboard? I am looking at your loop and it makes me unsure if you will fetch any file. Try changing it to this.

For Each vFile in FileList('$(DataPath)/Inventory/*')

TempInventory:
LOAD
   *
FROM
   [$(vFile)]
   (ooxml, embedded labels, header is 1 lines, table is Sheet1)
Where
   RecNo() > 1
   and Len(Trim("Company Code"))>0
   and MonthStart(Date(Date#(Mid(FileName(), 24, 8), 'MMM YYYY'))) >= '$(vLast36Month)';

Next vFile

[diegozecchini]

Hi!
Here a revised approach
Load All Data with Proper Date Parsing Ensure that the date fields are properly parsed during the load, so they can be filtered in the dashboard. Here's a modified script for your For Each loop:

// Loop through all Excel files in the folder
For Each vFile in FileList('$(DataPath)/Inventory/')

// Temporary table to load each file's data
TempInventory:
LOAD
*,
// Extract the month and year from the file name or date field
Date(Date#(Mid(FileName(), 24, 8), 'MMM YYYY')) AS FileDate,
Month(Date(Date#(Mid(FileName(), 24, 8), 'MMM YYYY'))) AS FileMonth,
Year(Date(Date#(Mid(FileName(), 24, 8), 'MMM YYYY'))) AS FileYear
FROM [$(vFile)]
(ooxml, embedded labels, header is 1 lines, table is Sheet1)
WHERE RecNo() > 1 AND Len(Trim("Company Code")) > 0;

Next;

// Combine all temporary data into a single table
Inventory:
NOCONCATENATE LOAD *
RESIDENT TempInventory;

// Drop the temporary table
DROP TABLE TempInventory;
This script ensures:

Each file's data is loaded and includes FileDate, FileMonth, and FileYear fields extracted for filtering.
All data is combined into a single table.
Filter Data in the Dashboard In the dashboard, use the FileMonth field to filter the data dynamically.