Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Luffy20
Contributor III
Contributor III

How to load 4 months data into data load editor

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;

Labels (5)
2 Replies
Vegar
MVP
MVP

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
Specialist
Specialist

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.