Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to manage with two Loads for one table

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!

1 Solution

Accepted Solutions

Re: How to manage with two Loads for one table

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?

18 Replies

Re: How to manage with two Loads for one table

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

Not applicable

Re: How to manage with two Loads for one table

Hi Pedro,

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

Joe

Not applicable

Re: How to manage with two Loads for one table

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

Not applicable

Re: How to manage with two Loads for one table

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?

Re: How to manage with two Loads for one table

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.

Not applicable

Re: How to manage with two Loads for one table

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.

Re: How to manage with two Loads for one table

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?

Not applicable

Re: How to manage with two Loads for one table

script error.png

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

Not applicable

Re: How to manage with two Loads for one table

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!

Community Browser