How to manage with two Loads for one table

[#1]

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!

[Peter_Cammaert]

What you can do is this:

• First read the date (second load) in a separate table (one line only)
• Put the date in a variable, say vTableDataDate using the peek function
• Drop the first table
• Now read the data, and add the date in variable vTableDataDate as an extra column

Example of the last step:

Data:

LOAD *,

     date($(vTableDataDate)) AS TableDate

FROM ...

Best,

Peter

[#3]

Hi Pedro,

something like the attached should work for you, hope that helps

Joe

[#4]

But there will be many files (i just put one as example). Will that work? Dont we need a loops function?

[#5]

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?

[Peter_Cammaert]

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.

[#7]

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.

[Peter_Cammaert · Accepted solution]

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?

[#9]

It gives me this error. I dont know why since I creat a table for each loop  and then I drop it.

[#10]

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!