Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
jozisvk11
Creator
Creator

Join 2 tables

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

1 Solution

Accepted Solutions
rahulpawarb
Specialist III
Specialist III

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

View solution in original post

9 Replies
Anonymous
Not applicable

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
Partner - Master
Partner - Master

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
Creator
Creator
Author

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
Partner - Master
Partner - Master

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
Specialist III
Specialist III

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
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jozisvk11
Creator
Creator
Author

Thank you very much Rahul

jozisvk11
Creator
Creator
Author

Yes, thank you