Join 2 tables

[jozisvk11]

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

[Anonymous]

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

        ];

[stabben23]

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;

[jozisvk11]

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

[stabben23]

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

[rahulpawarb · Accepted solution]

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

[Kushal_Chawda]

Like this

Table:

LOAD [Sales ID],

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

FROM Table1

GROUP By [Sales ID];

[jonathandienst]

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.

[jozisvk11]

Thank you very much Rahul

[jozisvk11]

Yes, thank you