topic Re: How to manage with two Loads for one table in QlikView

How to manage with two Loads for one table

Thu, 29 Jan 2015 17:22:39 GMT

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!

Re: How to manage with two Loads for one table

Peter_Cammaert — Thu, 29 Jan 2015 17:29:10 GMT

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

Re: How to manage with two Loads for one table

Thu, 29 Jan 2015 17:37:16 GMT

Hi Pedro,

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

Joe

Re: How to manage with two Loads for one table

Thu, 29 Jan 2015 18:30:22 GMT

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

Re: How to manage with two Loads for one table

Thu, 29 Jan 2015 18:31:44 GMT

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

Peter_Cammaert — Thu, 29 Jan 2015 18:57:43 GMT

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.

Re: How to manage with two Loads for one table

Thu, 29 Jan 2015 19:32:23 GMT

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

Peter_Cammaert — Thu, 29 Jan 2015 19:57:33 GMT

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?

Re: How to manage with two Loads for one table

Fri, 30 Jan 2015 10:16:31 GMT

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

Re: How to manage with two Loads for one table

Fri, 30 Jan 2015 10:19:59 GMT

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!

Re: How to manage with two Loads for one table

Fri, 30 Jan 2015 10:31:39 GMT

I figure out the problem! I changed the drop. It was between the two LOADS and now I put it after the secound LOAD.

Many thansk Peter you have been very helpful!

Re: How to manage with two Loads for one table

Peter_Cammaert — Fri, 30 Jan 2015 10:34:36 GMT

Something is wrong with your first table. After 2 steps, the script seems to store the Date value in table Table1-1 instead of Table1.

My suggestion: add a specific table name to your second LOAD, for instance DATA:

Re: How to manage with two Loads for one table

Fri, 30 Jan 2015 10:57:54 GMT

Do you think I should do that even though now the script it is working alright? I just changed the location of the drop, and then it started to work OK

Re: How to manage with two Loads for one table

Peter_Cammaert — Fri, 30 Jan 2015 11:47:05 GMT

Well, when you succeed in loading your text file data into a table, what will be your next step? You won't be able to use the table because it's name will be unknown and may vary with the set of files you are reading.

At the moment, I guess it gets its name from the first file you are loading. If this doesn't worry you, then don't change it.

Best,

Peter

Re: How to manage with two Loads for one table

Fri, 30 Jan 2015 14:44:08 GMT

You're completly right. I follow you advise I put a name on the table.

I used the following name:

[$(vFileName)]:

,though it returns me all the path. How can I select just the name of the file without the rest of the path? e.g.

the output of that tables is: "c:\Documents and Settings\12341008\Desktop\Espaço em Disco\Data\Producao NonStop 20150105.txt". And I just want "Producao NonStop 20150105." for the name of the file.

Can you briefly explain why we use the - $ - before the object?

And why the code: ($(vFileDate)) AS Day didn't work but the code date('$(vFileDate)') AS Day worked fine on my script?

Re: How to manage with two Loads for one table

Fri, 30 Jan 2015 14:53:31 GMT

And on this script all my tables come with the same path (which is the path form the first file, I dont know why it doesnt change while the loop is running). Any thoughts?

My current code:

FOR

Each vFileName in FileList ('.\Data\Producao NonStop*.txt')

// Extract date
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;

// Extract data from file +date
[$(vFileName)]:
LOAD @1 as Volume,
@3 as 'Total Capacity',
@4 as Free,
@5 as 'Free(%)',
@6 as Count,
@7 as Biggest,
('$(vFileDate)') AS Day // day added to the table
FROM
[$(vFileName)]
(txt, codepage is 1252, no labels, delimiter is spaces, msq, header is 5 lines);

NEXT vFileName

Re: How to manage with two Loads for one table

Peter_Cammaert — Fri, 30 Jan 2015 15:40:52 GMT

I guess something like

[$(=FileBaseName('$(vFileName)'))]:

should do the job. But why do you want to load every file in a different table? Why not all in the same table, and add a column that identifies the source file (using the same FileBaseName expression)

It's not just the $-sign, it's the $ and the parentheses that force Dollar-sign expansion. Upon evaluation, every single statement goes thru some sort of pre-processor that expands all $() elements into their current content. See Dollar-sign expansion in QV Desktop Help.

$(vFileDate) expands into 13/5/2015 which is an expression leading to a very small number.

'$(vFileDate)' expands into '13/5/2015' which is a string that is recognized by QlikView as a date.

Re: How to manage with two Loads for one table

Peter_Cammaert — Fri, 30 Jan 2015 15:43:19 GMT

Use the script debugger to step through your script and observe what is happening during each cycle.

Good luck,

Peter

Re: How to manage with two Loads for one table

Tue, 03 Feb 2015 16:49:29 GMT

Hi Peter,

Do you have any idea how can I add another field which is the volume from the last day?

I am adding something like this at the end of the script:

LOAD Free as PreviousFree

Resident Data

//Where Day = Max(Date(Day)-1) and Volume =

I dont know if I have to do 2 condition or one it is just enough (or should I do one condition and then group by Day and Volume).

I already figured out the logic behind the condition it is to calculate the Maximum from Date(Day)-1. Do you have any suggestion?

Thanks one more time for the help!