Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Please attach sample or excel files
vikas
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
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.
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)
Check this
Hi
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
Hi Girish,
Refer the following qvw
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;