Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have excel sheet, which will be deleted and new data will be loaded every day.
sample data
ID Model year and so on are the columns names
Excel generated on 28th-Jan
ID | Model | Year |
1 | asdf | 2011 |
123 | QWSDF |
2010 |
but there is no date field in the excel
Every day around 4pm, the client deletes the data and updates it with new set of records.
my question here is it doesn't have the date field in it
we are downloading the data in excel, Manually adding the date field(if generate on 28 then 28/1/2023, on 29th then 29/1/2023) to it and loading it in to the Qlik data.
Do we have an Automated way to add the Date field in the qliksense? and load all the excel files in to qlik sense dashboard?
Daily data
Excel generated on 28th-Jan
ID | Model | Year |
1 | asdf | 2011 |
123 | QWSDF |
2010 |
Excel generated on 29th-Jan
ID | Model | Year |
234 | ghj | 2011 |
456 | fgh |
2013 |
Excel generated on 30th-Jan
ID | Model | Year |
789 | klp | 2020 |
1011 | oiu | 2021 |
Expected output
Date | ID | Model | Year |
28/1/2023 | 1 | asdf | 2011 |
28/1/2023 | 123 | QWSDF |
2010 |
29/1/2023 | 234 | ghj | 2011 |
29/1/2023 | 456 | fgh |
2013 |
30/1/2023 | 789 | klp | 2020 |
30/1/2023 | 1011 | oiu | 2021 |
The row will be deleted and new records will be replaced EVERY DAY 4PM
I need to add the DATE to the existing excel file and load all the information into 1 single table.
Can anyone help me with the solution,
Appreciate for your help.
you can either handle historical with
FileTime( ) function instead of today.
Or extract the date from the filename
Subfield(Subfield(FileName()'_',3)'.',1) as "Date Loaded"
Try
New File:
Load
*,
Today() as "Date Loaded"
From your excel file;
It's working for today's file but I'm having historical files how to handle them.
we're currently downloading the data set into a folder naming it as
Model_Data_28-12-2023.xlsx
Model_Data_29-12-2023.xlsx
Model_Data_30-12-2023.xlsx
you can either handle historical with
FileTime( ) function instead of today.
Or extract the date from the filename
Subfield(Subfield(FileName()'_',3)'.',1) as "Date Loaded"