Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am looping through a folder in which the naming convention of files is structured.
1_filename.xlsx
2_filename.xlsx
3_filename.xlsx
4_filename.xlsx
I only want to load certain files from the folder so right now I have a conditional statement
For Each File in FileList('lib://Qlik Control Data/*.xlsx')
// create conditional statement to only load 1_filename.xlsx and 2_filename.xlsx
If Match(Right(Subfield('$(File)','_',1),4),'1','2') Then
LOAD
*
FROM ['$(File)']
(ooxml, embedded labels, table is CASHFLOWS)
;
End If;
Next File;
This works well except for the fact I had to hard code 1 and 2 into my match statement.
What I would prefer to do is store 1,2 as a variable but I am unsure how to. Prior to my loop, a table in the script is generated which identifies 1_filename.xlsx and 2_filename.xslx as the files that we want to be loaded. I.e.
FileName | Include | Prefix |
---|---|---|
1_filename.xlsx | Y | 1 |
2_filename.xlsx | Y | 2 |
3_filename.xlsx | N | 3 |
4_filename.xlsx | N | 4 |
Using this table how could I create a variable in the script that stores the value '1,2'
Thanks!
Mark
Figured it out
Load
Concat(Chr(39) & Prefix & Chr(39),',') as PrefixList
Resident PrefixTable;
Drop Table Prefix Table;
let vPrefixList = FieldValue('PrefixList',1);
Hi All,
So I've gotten to this point
I've eliminated records where Include = N from my table and created the below loop
Let vFilePrefix = FieldValueCount('Prefix')
Do While i <= '$(vFilePrefix)'
let i = 1;
let vTest = FieldValue('Prefix','$(i)');
let i = i+1;
loop
So this will give me the last possible value of Prefix, in this case 2. Is it possible for me to append strings to an existing variable without wiping out the value?
Thanks!
Figured it out
Load
Concat(Chr(39) & Prefix & Chr(39),',') as PrefixList
Resident PrefixTable;
Drop Table Prefix Table;
let vPrefixList = FieldValue('PrefixList',1);