Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone!! A few days ago I have joined this community to which, every day, I am surprised more and more.
Wanting to do a test with my data I have found that I can not overcome this question that I tell you below.
I have a network disk folder a series of files, XLS, CSV and TXT that always have the same name and in case of being updated, they are replaced by a file with the same name. These data are used as dimensions.
But I have a folder where month to month files "report_2017_01" for January, "report_2017_02" for February and so on each month.
These files always have the same column structure and are therefore added one after another.
So far I have not found a way for Qlik to add me the data without having to touch the Data Model by hand.
Qlik can not solve it?
For the doubts I tell you that the way I could solve it was by using the PowerQuery (Excel add-in) which, if I told you to take data from a folder and find files that have the same structure, join them all in a single table . Got this what I do is for Qlik to read that table. 😞 but I do not like anything.
A big greeting for the whole community.
Martin.
Hello Martin,
You can refer below given sample script:
//This will load all the files which begins with report_ name
Data:
LOAD Id,
...
report_*.xlsx
(ooxml, embedded labels, table is [Sheet1]);
Hope this will be helpful.
Regards!
Rahul
Hello Martin,
You can refer below given sample script:
//This will load all the files which begins with report_ name
Data:
LOAD Id,
...
report_*.xlsx
(ooxml, embedded labels, table is [Sheet1]);
Hope this will be helpful.
Regards!
Rahul
You can use a wildcard load or a loop to load and concatenate multiple files with a similar structure. So for your monthly data files, you could load like this:
Fact:
LOAD 0 as Dummy Autogenerate 0;
Concatenate(Fact)
LOAD *,
FileBaseName() as Source
FROM ...\*.xls
DROP Field Dummy;
If the date is not in the file, then you could parse the file name (in FileBaseName()) for the date; something like:
Date(Date#(Mid(FileBaseName(), 8, 7), 'yyyy_MM')) as Date,