Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

laujerry
Not applicable

join multiple files at the same time with asterisk (*)

hi all

just want to understand that if below joining script is not supported by qlikview / qliksense joining (now i am using qlikview)

thank

table1:

load ....

from abc.qvd(qvd);

join (table1)

load ...

from file_*.qvd(qvd);

i found that not all file_*.qvd are loaded/joined

i also tried writing with a preceding load but also not work

join (table1)

load *;

load ...

from file_*.qvd(qvd);

1 Solution

Accepted Solutions
swuehl
Not applicable

Re: join multiple files at the same time with asterisk (*)

Just swapping the order of joined tables should do it:


Tbl_1:
LOAD 1 as A,
     'A' as Key
AutoGenerate 1;

STORE Tbl_1 into Tbl_1.qvd;

DROP Table Tbl_1;


Tbl_2:
LOAD 2 as A,
     'B' as Key
AutoGenerate 1;

STORE Tbl_2 into Tbl_2.qvd;

DROP Table Tbl_2;

Tbl:
LOAD A,
     Key
From Tbl_*.qvd(qvd);

RIGHT JOIN (Tbl)
LOAD * Inline [
   F1, A
   1, 1
   2, 2
];

11 Replies
sunny_talwar
Not applicable

Re: join multiple files at the same time with asterisk (*)

Try this:

table1:

load ....

from abc.qvd(qvd);

Table2:

load ...

from file_*.qvd(qvd);


Join(table1)

LOAD *

Resident Table2;


DROP Table Table2

laujerry
Not applicable

Re: join multiple files at the same time with asterisk (*)

hello sunny

thank you for your reply

i know this will work

but i dont want to do this every time

so i am asking if qlik does not support "load *" when performing joining as the second table

Digvijay_Singh
Not applicable

Re: join multiple files at the same time with asterisk (*)

I tried below on sample data, it worked -

table1:

LOAD Name,

     Value

FROM

table1.qvd

(qvd);

join(table1)

LOAD Name,

     Value

FROM

table_*.qvd

(qvd);

maxgro
Not applicable

Re: join multiple files at the same time with asterisk (*)

could you post a small example of your problem?

hirishv7
Not applicable

Re: join multiple files at the same time with asterisk (*)

Hi,

Can you post your example . and required output!!

LOAD data into QlikView

HTH,

Hirish

“Aspire to Inspire before we Expire!”
swuehl
Not applicable

Re: join multiple files at the same time with asterisk (*)

I guess in this case:

table1:

LOAD Name,

     Value

FROM

table1.qvd

(qvd);

join(table1)

LOAD Name,

     Value

FROM

table_*.qvd

(qvd);

the resulting table is due to auto-concatenation of tables, not joining.

[edit: No, it seems that it's getting joined, but the following statement regarding key fields for joining should still be valid]

"so i am asking if qlik does not support "load *" when performing joining as the second table"

Well, it does support a wild card load, but the LOAD and JOIN is performed input table wise (like if you write your statement explicitely for each input table file. So what you probably see is that the first table get joined, but then all other input tables won't because now the join key considers already the fields joined with the first input table load.

(Look at the resulting table after the first file is loaded and joined, then try to figure out how the second file is loaded and which keys are relevant for a join).

So, to make it short, I think you should go with something like Sunny suggested in the first answer.

swuehl
Not applicable

Re: join multiple files at the same time with asterisk (*)

Another option could be to swap the table LOAD order:

load ...

from file_*.qvd(qvd);

JOIN

load ....

from abc.qvd(qvd);

martinpohl
Not applicable

Re: join multiple files at the same time with asterisk (*)

you cannot do it in other way.

What happens:

You load first table, all values are in.

Now by using left join the first joined qvd will be joined to your table and creates the new fields from the qvd.

Then the second qvd should be joined. But in that case the new fields from second qvd exists in joined table. In that case qlik is joining all fields from the second table because all fields are existsing in table1.

But now there are no datalines in both tables which contains the same datas (what is the requirement to be joined).

So you won't get any datas from the other files than file one.

Regards

laujerry
Not applicable

Re: join multiple files at the same time with asterisk (*)

Tbl_1:

LOAD 1 as A,

     'A' as Key

AutoGenerate 1;

STORE Tbl_1 into Tbl_1.qvd;

DROP Table Tbl_1;

Tbl_2:

LOAD 2 as A,

     'B' as Key

AutoGenerate 1;

STORE Tbl_2 into Tbl_2.qvd;

DROP Table Tbl_2;

Tbl:

LOAD * Inline [

   F1, A

   1, 1

   2, 2

];

Left Join (Tbl)

LOAD A,

     Key

From Tbl_*.qvd(qvd);

expected result

F1, A, Key

1, 1, A

2, 2, B