Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm looking for a way to only load files where they exist in a table similar to the below where the ACTIVE flag is set to 1.
AREA | ACTIVE | FILENAME |
---|---|---|
North | 1 | 201510_North_Party.txt |
South | 1 | 201510_South_Party.txt |
East | 0 | 201510_East_Party.txt |
West | 1 | 201510_West_Party.txt |
Is there a way to have one load statement that loops through the table and loads the 3 files?
thanks
Stuart
Check this out:
Table:
LOAD Concat(FILENAME, '|') as List,
Count(FILENAME) as Count
FROM
[https://community.qlik.com/thread/200275]
(html, codepage is 1252, embedded labels, table is @1)
Where ACTIVE = 1;
LET vList = Chr(39) & Peek('List') & Chr(39);
LET vCount = Peek('Count');
DROP Table Table;
FOR i = 1 to $(vCount)
LET vFile = SubField($(vList), '|', $(i));
Table:
LOAD Dim,
Value
FROM
$(vFile)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
NEXT i
I guess create a concatenated list of FILENAME to be loaded in and loop through those.
yes - I am unsure how to get the load statement to loop on a list of filenames?
I am working on a sample for you to look at
awesome
Try like below:
FilesToBeLoad:
Load Distinct
FILE_NAME
From
source_meta_data
Where
ACTIVE =1
;
For i=1 to FieldValuecount('FILE_NAME')
Let vFileName = FieldValue('FILE_NAME',$(i))
FinalTable:
Load
*
From [$(vFileName)] (txt)
Next i
Check this out:
Table:
LOAD Concat(FILENAME, '|') as List,
Count(FILENAME) as Count
FROM
[https://community.qlik.com/thread/200275]
(html, codepage is 1252, embedded labels, table is @1)
Where ACTIVE = 1;
LET vList = Chr(39) & Peek('List') & Chr(39);
LET vCount = Peek('Count');
DROP Table Table;
FOR i = 1 to $(vCount)
LET vFile = SubField($(vList), '|', $(i));
Table:
LOAD Dim,
Value
FROM
$(vFile)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
NEXT i
dathu.qv what happens if there is duplicates in FILE_NAME field? Does it take the distinct count and distinct FieldValue? If it doesn't take distinct by default, is there a way to make it distinct? May be like FieldValueCount(DISTINCT 'FILE_NAME') or something along these lines??
thank you so much for the help -works excellently