topic Re: Append / Join two tables in QlikView

Append / Join two tables

girish_talele — Mon, 05 Jan 2015 05:06:47 GMT

Dear Experts,

I have two tables i.e. "Order Pool" & "Relation".

In first table list of order is there & in second table relation between Main Material & Sub Material is there.

As a Result, we want the data as shown in Result Table.

I failed to achieve the same using JOIN methods. Please suggest how to map the requirement.

Re: Append / Join two tables

avinashelite — Mon, 05 Jan 2015 05:10:14 GMT

HI Girish,

OrderPool:

Load Order_main,

Main_mat ,

Field1,

Field2

from OrderPool;

Join(OrderPool:

Load Order_main,

Main_mat as Key,

Field1,

Field2

from OrderPool;)

Relation:

Load

Main_mat,

Sub_Mat,

Field3,

Field4

from OrderPool;

Result:

Load Order_main,

Main_mat as key,

Field1,

Field2,

Field3,

Field4,

resident OrderPool;

Concatenate(Result)

Load Order_main,

Sub_Mat as key,

Field1,

Field2,

Field3,

Field4,

resident OrderPool;

drop table OrderPool;

Re: Append / Join two tables

vikasmahajan — Mon, 05 Jan 2015 05:13:43 GMT

Please attach sample or excel files

vikas

Re: Append / Join two tables

girish_talele — Mon, 05 Jan 2015 05:20:07 GMT

Dear Avinash,

Simple Left Join is not working it is giving result as given below :-

Dear Vikas,

The qvw is attached herewith.

Thanks for looking into,

Girish

Re: Append / Join two tables

jagan — Mon, 05 Jan 2015 05:20:10 GMT

Hi,

Try like this

OrderPool:

LOAD

FROM OrderPool;

LEFT JOIN (OrderPool)

LOAD

FROM Relation;

Concatenate(OrderPool)

LOAD

FROM OrderPool;

Hope this helps you.

Regards,

Jagan.

Re: Append / Join two tables

girish_talele — Mon, 05 Jan 2015 05:29:47 GMT

Dear Jagan,

Thanks, but the results are same.

The requirement is to Append the Sub-Materials below the Main Materials as given below :-

(Correction in Main Order 6 & 7 is done which was missing in earlier snap)

Re: Append / Join two tables

Anonymous — Mon, 05 Jan 2015 06:01:14 GMT

Check this

Re: Append / Join two tables

jonathandienst — Mon, 05 Jan 2015 06:12:22 GMT

This will do it:

OrderPool:

LOAD Ord_Main,

Main_Mat,

Field1,

Field2

FROM

BOMJoin.xlsx

(ooxml, embedded labels, table is OrderPool);

Right JOIN (OrderPool)

LOAD Main_Mat,

Sub_Mat,

Field3,

Field4

FROM

BOMJoin.xlsx

(ooxml, embedded labels, table is Relation);

Concatenate(OrderPool)

LOAD Ord_Main,

Main_Mat,

Field1,

Field2,

'' As Sub_Mat,

'' As Field3,

'' As Field4

FROM

BOMJoin.xlsx

(ooxml, embedded labels, table is OrderPool);

To merge the Main_Mat and Sub_Mat in a straight or pivot table table, use the calculated dimension:

=If(Len(Sub_Mat) = 0, Main_Mat, Sub_Mat)

HTH

Jonathan

Re: Append / Join two tables

Mon, 05 Jan 2015 06:26:29 GMT

Hi Girish,

Refer the following qvw

Re: Append / Join two tables

Mon, 05 Jan 2015 07:03:48 GMT

One right join and One outer join.

OrderPooltmp:

LOAD * INLINE [

Ord_Main, Main_Mat,Field1,Field2

1, Mat 1, AAA, aaa

2, Mat 2, BBB, bbb

3, Mat 3, CCC, ccc

4, Mat 4, DDD, ddd

5, Mat 5, EEE, eee

6, Mat 1, FFF, fff

7, Mat 2, GGG, ggg

];

right join

LOAD * INLINE [

Main_Mat, Sub_Mat, Field3, Field4

Mat 1, Sub_Mat 1.1, HHH, hhh

Mat 1, Sub_Mat 1.2, III, iii

Mat 2, Sub_Mat 2.1, JJJ, jjj

Mat 2, Sub_Mat 2.2, KKK, kkk

Mat 2, Sub_Mat 2.3, LLL, lll

Mat 3, Sub_Mat 3.1, MMM, mmm

Mat 3, Sub_Mat 3.2, NNN, nnn

];

finalresult:

load Ord_Main,Main_Mat,Field1,Field2 resident OrderPooltmp;

outer join

load Ord_Main,Sub_Mat as Main_Mat,Field1,Field2,Field3,Field4 resident OrderPooltmp;

drop table OrderPooltmp;

Re: Append / Join two tables

Mon, 05 Jan 2015 07:15:09 GMT

Hi Girish ,

Here is corrected version , Result data is now showing correct output for Order_Main 4 and 5

Re: Append / Join two tables

girish_talele — Mon, 05 Jan 2015 07:29:14 GMT

Dear Experts,

Thanks to all, Almost all solutions are matching the end result.

Let me tried the same in actual scenario, will get back to you soon.

Thanks,

Girish

Re: Append / Join two tables

Mon, 05 Jan 2015 07:47:12 GMT

Check this one, results are as you were expecting.

Orders:

LOAD * INLINE

[Ord_Main,Main_Mat,Field1,Field2

1,Mat 1,AAA,aaa

2,Mat 2,BBB,bbb

3,Mat 3,CCC,ccc

4,Mat 4,DDD,ddd

5,Mat 5,EEE,eee

6,Mat 1,FFF,fff

7,Mat 2,GGG,ggg];

JOIN

LOAD Num#(mid(Sub_Mat,9,1),'0') as Ord_Main,

Sub_Mat as Main_Mat,

*;

LOAD *

INLINE

[Main_Mat_Relation,Sub_Mat,Field3,Field4

Mat 1,Sub_Mat 1.1,HHH,hhh

Mat 2,Sub_Mat 1.2,III,iii

Mat 3,Sub_Mat 2.1,JJJ,jjj

Mat 4,Sub_Mat 2.2,KKK,kkk

Mat 5,Sub_Mat 2.3,LLL,lll

Mat 1,Sub_Mat 3.1,MMM,mmm

Mat 2,Sub_Mat 3.2,NNN,nnn];

Result:

NoConcatenate

LOAD

Ord_Main,

Main_Mat,

Sub_Mat,

If(Ord_Main=Peek(Ord_Main),Peek(Field1),Field1) as Field1,

If(Ord_Main=Peek(Ord_Main),Peek(Field1),Field2) as Field2,

Field3,

Field4

Resident

Orders;

Drop Table Orders;

Re: Append / Join two tables

Mon, 05 Jan 2015 08:00:01 GMT

Correction to show the materials on Orders that are not on the material tables:

OrderPooltmp:

LOAD * INLINE [

Ord_Main, Main_Mat,Field1,Field2

1, Mat 1, AAA, aaa

2, Mat 2, BBB, bbb

3, Mat 3, CCC, ccc

4, Mat 4, DDD, ddd

5, Mat 5, EEE, eee

6, Mat 1, FFF, fff

7, Mat 2, GGG, ggg

];

left join

LOAD * INLINE [

Main_Mat, Sub_Mat, Field3, Field4

Mat 1, Sub_Mat 1.1, HHH, hhh

Mat 1, Sub_Mat 1.2, III, iii

Mat 2, Sub_Mat 2.1, JJJ, jjj

Mat 2, Sub_Mat 2.2, KKK, kkk

Mat 2, Sub_Mat 2.3, LLL, lll

Mat 3, Sub_Mat 3.1, MMM, mmm

Mat 3, Sub_Mat 3.2, NNN, nnn

];

finalresult:

load Ord_Main,Main_Mat,Field1,Field2 resident OrderPooltmp ;

outer join

load Ord_Main,Sub_Mat as Main_Mat,Field1,Field2,Field3,Field4 resident OrderPooltmp where IsNull(Sub_Mat)=0;

drop table OrderPooltmp;