Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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!

18 Replies
Not applicable
Author

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!

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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:

Not applicable
Author

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Not applicable
Author

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?

Not applicable
Author

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Good luck,

Peter

Not applicable
Author

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!