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

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

laujerry — Sat, 05 Mar 2016 01:51:55 GMT

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);

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

sunny_talwar — Sat, 05 Mar 2016 03:20:17 GMT

Try this:

table1:

load ....

from abc.qvd(qvd);

Table2:

load ...

from file_*.qvd(qvd);

Join(table1)

LOAD *

Resident Table2;

DROP Table Table2

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

laujerry — Sat, 05 Mar 2016 04:29:37 GMT

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

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

Digvijay_Singh — Sat, 05 Mar 2016 05:49:39 GMT

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);

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

maxgro — Sat, 05 Mar 2016 08:33:19 GMT

could you post a small example of your problem?

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

HirisH_V7 — Sat, 05 Mar 2016 08:58:48 GMT

Hi,

Can you post your example . and required output!!

LOAD data into QlikView

HTH,

Hirish

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

swuehl — Sat, 05 Mar 2016 09:58:58 GMT

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.

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

swuehl — Sat, 05 Mar 2016 10:56:41 GMT

Another option could be to swap the table LOAD order:

load ...

from file_*.qvd(qvd);

JOIN

load ....

from abc.qvd(qvd);

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

martinpohl — Sat, 05 Mar 2016 12:05:14 GMT

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

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

laujerry — Sun, 06 Mar 2016 12:46:33 GMT

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

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

swuehl — Sun, 06 Mar 2016 13:05:46 GMT

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
];

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

laujerry — Sun, 06 Mar 2016 14:27:01 GMT

thanks all input from u

this looks like a more preferable solution to me