Firstly thank you for taking the time to read this.
I'm coming from Power BI & Excel, to Qlik Sense and I'm currently having difficulty with the following: -
Loading all CSVs from a folder, pull data out from each files name and use this as a data column
From the file name
Extract the text between two characters
Transform this text to a date data type
Place these dates as a column into the data for that file, repeating the date for in row in the file
Repeat this for all files in the folder
Be able to filter out key file/s from the this process, and from the data load operation
With Power BI or Excel, the ETL tool Power Query/Query Editor makes this so easy, via a GUI driven process. With Qlik Sense I'm rather stumped.
I understand the process for loading from a file (using the wildcard * on the file name), but I can't see how to combine this with my above requirement.
Basically something like if you do a wildcard load
MyData:
LOAD
*,
FileName() as Source,
Date(Date#(TextBetween(FileName(), '<', '>'), 'YYYYMMDD'),'MM/DD/YYYY') as DateFromFileName
FROM
[LIB://MyCSVFiles\*.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq)
;
Replace < , >, YYYYMMDD and MM/DD/YYYY with the appropriate characters and date format strings you need.
If you want to exclude specific files then you could do two things:
1. Load all the data and then use a preceding load to filter out the data from those specific files
MyData:
LOAD * WHERE NOT WildMatch(Source, '*MySpecificFile1.csv','*MySpecificFile2.csv') ;
LOAD
*,
FileName() as Source,
Date(Date#(TextBetween(FileName(), '<', '>'), 'YYYYMMDD'),'MM/DD/YYYY') as DateFromFileName
FROM
[LIB://MyCSVFiles\*.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq)
;
2. Use a For - Next loop and add an If-then within the loop with conditions to filter out the files you don't want to load.
