Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone!
I am doing a script on Qlikview to extract data from .txt files. I already figure out out to extract the data just fine using two different Loads.
I solve my problem to extract the data by doing two differents:
-one to extract the date on the header of the file; aka Step 1
-the other to extract the remaining data. aka Step 2
However, I have a problem because now I want to do a table with both informations. I want one table with the data that I extracted (on step 2) plus the date of the file (step 1), in order to do some reports. In others words, I want my table to have the data from step plus one field with the date which I extract from the file (step 1).
I send you my code as well as a random file.
I hope you can understand want I mean and you can help me out.
Thanks in advance!
It works in my Desktop with your example text file (if I change the filemask from Producao into Qualidade). The Day field contains invalid data, though. Change date($(vFileDate)) AS Day into date('$(vFileDate)') AS Day.
Maybe you should add a table name before the second LOAD. Data will AUTOCONCATENATE during each file load.
Can you tell me what error messages you are getting?
What you can do is this:
Example of the last step:
Data:
LOAD *,
date($(vTableDataDate)) AS TableDate
FROM ...
Best,
Peter
Hi Pedro,
something like the attached should work for you, hope that helps
Joe
But there will be many files (i just put one as example). Will that work? Dont we need a loops function?
Joe thanks for the answear! I cant open the file though, I reach the maximum "opens" for my free license. Can you send me the code in a .txt file?
Yes it will work, and yes you'll need a FOR loop, optionally controlled by the FileList() and DirList masks to walk a complete directory or a directory tree. See Desktop help for the FOR EACH statement for an example that reads files from a directory and processes them.
The LOAd statement also supports wildcards in the FROM clause, but you cannot use those because you'll loose the opportunity to parse the embedded date and attach it to the corresponding rows.
And where should I begin the loop?
Should I open the loop and put both Loads inside? I shold I change the FROM?
"
FOR Each vFileName in FileList ('.\Data\Producao NonStop*.txt')
Table1:
LOAD @10 as FileDay
FROM
[$(vFileName)]
(txt, codepage is 1252, no labels, delimiter is spaces, msq)
WHERE(Len(Trim(@10))>0);
LET vFileDate = Peek('FileDay',0,'Table1');
DROP table Table1;
LOAD @1 as Volume,
@3 as 'Total Capacity',
@4 as Free,
@5 as 'Free(%)',
@6 as Count,
@7 as Biggest,
date($(vFileDate)) as Day
FROM
[$(vFileName)]
(txt, codepage is 1252, no labels, delimiter is spaces, msq, header is 5 lines);
NEXT vFileName
"
I used this code but it gave several errors.
It works in my Desktop with your example text file (if I change the filemask from Producao into Qualidade). The Day field contains invalid data, though. Change date($(vFileDate)) AS Day into date('$(vFileDate)') AS Day.
Maybe you should add a table name before the second LOAD. Data will AUTOCONCATENATE during each file load.
Can you tell me what error messages you are getting?
It gives me this error. I dont know why since I creat a table for each loop and then I drop it.
What is weird is that know I have inside the folder (data) 8 files and this script error only happens 5 times!
Peter many thanks for your help!