topic Re: Join 2 tables in QlikView

Join 2 tables

jozisvk11 — Fri, 03 Feb 2017 07:57:38 GMT

Hi friends, i need join 2 tables :

1. table 2. table

Sales ID Sales ID Row Order ID

P11 P11 10 T101601

P12 P11 20 T101611

P13 P12 10 T101607

P14 P12 20 T101609

P15 P12 30 T101608

.... P13 10 T101605

.... P14 10 T101606

.... P14 50 T101602

Result table (I need):

Sales ID Order ID

P11 T101601, T101611

P12 T101607, T101609, T101608

P13 T101605

P14 T101606, T101602

Can anyone help me ? How can I do in script ? Thank you

Re: Join 2 tables

Anonymous — Fri, 03 Feb 2017 08:07:06 GMT

try this

table1:

LOAD * Inline

[

Sales ID

P11 ,

P12 ,

P13 ,

P14 ,

P15 ];

Inner join (table1)

Table2:

LOAD * Inline

[

Sales ID, Row ,Order ID

P11 , 10 , T101601

P11 , 20 , T101611

P12 , 10 , T101607

P12 , 20 , T101609

P12 , 30 , T101608

P13, 10 , T101605

P14, 10 , T101606

P14, 50 , T101602

];

Re: Join 2 tables

stabben23 — Fri, 03 Feb 2017 08:14:49 GMT

I'm not sure why you even want to do this, but here is some script which could helps you.

I should have use a normal qlikview assosiation.

table1:
Load * inline [
"Sales ID"
P11
P12
P13
P14
];

table2:
Load * Inline ["Sales ID" ,Row,"Order ID"
P11 , 10 , T101601

P11 , 20 , T101611

P12 , 10 , T101607

P12 , 20 , T101609
P12 , 30 , T101608
P13 , 10 , T101605
P14 , 10 , T101606
P14, 50 , T101602
];

Left Join (table1) Load "Sales ID", Concat("Order ID",',') as "Order ID"
Resident table2 Group by "Sales ID";

drop table table2;

Re: Join 2 tables

jozisvk11 — Fri, 03 Feb 2017 08:20:45 GMT

I have big tables. I can´t write this how you wrote. 1. tables has 30 000 rows, 2. tables has 120 000 rows.

Re: Join 2 tables

stabben23 — Fri, 03 Feb 2017 08:27:49 GMT

Of cource you cant, its just from Your example,

Use Your own tables and just the last part.

Left Join (table1) Load "Sales ID", Concat("Order ID",',') as "Order ID"
from table2 Group by "Sales ID";

Re: Join 2 tables

rahulpawarb — Fri, 03 Feb 2017 08:46:45 GMT

You can use below (if your source is excel else modify the from clause to get expected results):

Table1:
LOAD [Sales ID]
FROM

(ooxml, embedded labels, table is Table1);
LEFT JOIN (Table1)
Table2:
LOAD [Sales ID],
     Concat("Order ID",',') as "Order ID"
FROM

(ooxml, embedded labels, table is Table2)
Group by [Sales ID];

Re: Join 2 tables

Kushal_Chawda — Fri, 03 Feb 2017 09:14:32 GMT

Like this

Table:

LOAD [Sales ID],

Concat(distinct [Order ID],',') as [Order ID]

FROM Table1

GROUP By [Sales ID];

Re: Join 2 tables

jonathandienst — Fri, 03 Feb 2017 09:19:50 GMT

You can join the tables if you want, but very probably, it is not necessary. QV/QS will automatically associate the two tables through the common key field [Sales ID]. It will also better handle duplicates which could cause double counting if you perform a join.

Re: Join 2 tables

jozisvk11 — Fri, 03 Feb 2017 12:44:04 GMT

Thank you very much Rahul

Re: Join 2 tables

jozisvk11 — Fri, 03 Feb 2017 12:44:31 GMT

Yes, thank you