2 tables Left Join + saving into qvd

[rb_cvogt]

Hi Qlik community,

I'm new here and to Qlik, so hopefully I give you enough information to help me.

My situation: I got two tables saved as a monthly Excel report from the beginning of 2019. Now i want to perform a left join with these two tables over all months and save them as one qvd.

The Debug mode gives me a ScriptErrorDetail taht says:  unexpected token: '-', expected one of: ',', 'as', 'from', 'into'

The errormessage comes at the italic / red highlighted Store command

My Code for loading the tables:

for each vFile in FileList('lib://DataSource_1/*.xls')

Files:

LOAD

'$(vFile)' as File

AutoGenerate 1;

Table_Name1:

LOAD

Attribut 1,

Attribut 2,

and so on...

FROM ['$(vFile)']

(biff, embedded labels, header is 5 lines, table is table1$);

Table_Name2:

Left Join

LOAD

Attribut 1,

Attribut 2,

and so on...

FROM ['$(vFile)']

(biff, embedded labels, header is 2 lines, table is table2$);

next vFile;

Drop table Files;

Next section - Store tables:

FOR vCount = 0 to NoOfTables()-1

LET vTableName = TableName($(vCount));

STORE $(vTableName) INTO '[lib://DataSource_1/$(vTableName)_test.qvd' (qvd);

NEXT vCount

FOR vCount = 0 to NoOfTables()-1

LET vTableName = TableName($(vCount));

Drop table $(vTableName);

NEXT vCount

As I understand the error, Qlik wants to  save the file as "Table_Name1-2" and that produces the unexpected token error.

Any suggestions to help me out with that?

Best regards,

Carsten

[Or]

Try putting the variable in quotes. If that doesn't work, try putting the full path into a variable first and then using that variable for the Store command.

FOR vCount = 0 to NoOfTables()-1

LET vTableName = TableName($(vCount));

STORE '$(vTableName)' INTO '[lib://DataSource_1/$(vTableName)_test.qvd' (qvd);

Or:

FOR vCount = 0 to NoOfTables()-1

LET vTableName = TableName($(vCount));

LET vPathName = '[lib://DataSource_1/' & '$(vTableName)' & '_test.qvd' ;

STORE '$(vTableName)' INTO '$(vPathName)' (qvd);

[rb_cvogt]

Thank you for helping, the code is running now a bit longer, but still with a problem.

So first of all:

Putting the $(vTableName) into quotes helped, but now at the end  where I drop the tables the loop seems to run more then it should. 

FOR vCount = 0 to NoOfTables()-1

LET vTableName = TableName($(vCount));

Drop table '$(vTableName)';

NEXT vCount

Errormessage by drop table: "Table  not found". 

[Or]

I'd guess either the vTableName variable isn't getting populated correctly, or that loop is inherently incorrect because each time you drop a table, the number of tables decreases, so you eventually exceed the total number of tables using vCount.

I'd suggest using Debug mode to step through your code and check what's happening to the variables through the iterations of your loop. It's much easier to debug that way than by just reading the code.

[rb_cvogt]

Thanks for the tip. But still I don't really understand what and why he's doing it the way he does 🙂 So sorry for that, I'm still learning....

But I'm with you, something with the iterations of my loop is not working probably.

He creates 34 individual qvd files. Is that because of the LeftJoin command? How I imagine this isnt necessary. He should save everything in one qvd file that I can load in.

The vCount stands still at 17 (exactly at the half) and then the error Table not found pops up. Any thoughts on this?

[Or]

This is exactly what I suggested in my previous post - because you are dropping a table through each iteration of the loop, you're actually changing the reference for TableName(). By the time you've gone through 17 iterations, you are trying to get Table 18, but there are only 17 tables left, so this doesn't work. You'll need to either not drop the tables immediately (you can loop and drop at the end) or avoid incrementing the counter (if you always go to the first table, and then drop it, then each time a new table becomes the first table).

I don't know why you (or whoever wrote the code) elected to use separate QVDs, but this is clearly intentional or they wouldn't have written a loop to store them all. You could load all the data into one table and store that if you wanted to, but no idea what other things this might impact for you.

[rb_cvogt]

Okay that explains my problem. The thing is, I copied from other Qlik files code that seems to fit my purpose. It's the way of my learning  by doing process. With your help I understand more and more.

Let's see if I find a way to solve my problem and I think I come my solution a step closer. Not finished yet, but it's already better 🙂

Many thanks to you and your time. Have a nice evening