Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Please help as I am lost with thinking on the below scenario
Table1:
Load * inline [
ID, Name1
10, ABC
11, ASE
12, SER
13, SEF
14, HJJ
];
Table2:
Load * inline [
ID1, ID2, Dater
09, 10, xxxxxxx
10, 09, xxxxxxx
08, 11, xxxxxx
11, 08, xxxxxx
];
The ask or the requirement is to prepare a table with the below fields
Table2.ID1 | Table2.ID1Name1 (aliased from Table1.Name1 ) | Table2.ID2 | Table2.ID2Name1 (aliased from Table1.Name1) | Dater
The conditions would be
Table2.ID1 ==> should show only the IDs which satisfies the condition Table1.ID = Table2.ID1
Table2.ID1Name1 ==> Based on Table1.ID = Table2.ID1 the relevant Table1.Name1 should be aliased
Table2.ID2 ==> should show only the IDs which satisfies the condition Table1.ID = Table2.ID2
Table2.ID1Name2 ==> Based on Table1.ID = Table2.ID2 the relevant Table1.Name1 should be aliased
Dater ==> Based on above filters the equivalent Dates should be shown.
-------------
The confusion is this can achieved by Join / have to concatenate / apply map will work??
Please help!
Regards,
M. Parvez
Not understood completely. but try below code
Table1:
Load * inline [
ID, Name1
10, xxx
11, xxx
12, xxx
13, xxx
14, xxx
];
Table2:
Load * inline [
ID1, ID2, Dater
21, 10, xxxxxxx
10, 11, xxxxx
12, 13, xxxxxxx
21, 11, xxxxx
];
NoConcatenate
Table3:
Load ID1 as newID1, Dater as newDater Resident Table2
where Exists(ID,ID1)
;
Left Join
Load ID as newID1, Name1 as Table2.ID1Name1 Resident Table1;
NoConcatenate
Table4:
Load ID2 as newID2, Dater as newDater1 Resident Table2
where Exists(ID,ID2)
;
Left Join
Load ID as newID2, Name1 as Table2.ID1Name2 Resident Table1;
NoConcatenate
Table5:
Load newID1 as ID1,Table2.ID1Name1, newDater as Dater Resident Table3;
Concatenate
Load newID2 as ID2,Table2.ID1Name2, newDater1 as Dater Resident Table4;
Drop Tables Table1,Table2,Table3,Table4;
Regards,
Prashant Sangle
No rows satisfies your set of criterias. You have no rows where both ID1 and ID2 have a match in the Table1 ID field.
Could you please show us the expected output table of your data sample?
Hi Vegar,
The same bothering me!
In general the table from right (Table2) should be compare with the left table(Table1) and should display only the records which are matching with left table(Table1) as left join.
Can you guide me in both the scenarios
1. The ID1, ID2 are having matching records with ID and can fetch the Name1
2. ID1, ID2 are not matching with ID
Example:
Table1:
ID, Name1
10, xxx
11, xxx
12, xxx
13, xxx
14, xxx
Table2:
ID1, ID2, Dater
21, 10, xxxxxxx
10, 11, xxxxx
12, 13, xxxxxxx
21, 11, xxxxx
Expected o/p
ID1, ID1 Name, ID2, ID2 Name
10, xxxx, Null / Blank (if not matched), Null
Null / Blank, Null, 11, xxxx
12, , xxxx, Null, Null
Not understood completely. but try below code
Table1:
Load * inline [
ID, Name1
10, xxx
11, xxx
12, xxx
13, xxx
14, xxx
];
Table2:
Load * inline [
ID1, ID2, Dater
21, 10, xxxxxxx
10, 11, xxxxx
12, 13, xxxxxxx
21, 11, xxxxx
];
NoConcatenate
Table3:
Load ID1 as newID1, Dater as newDater Resident Table2
where Exists(ID,ID1)
;
Left Join
Load ID as newID1, Name1 as Table2.ID1Name1 Resident Table1;
NoConcatenate
Table4:
Load ID2 as newID2, Dater as newDater1 Resident Table2
where Exists(ID,ID2)
;
Left Join
Load ID as newID2, Name1 as Table2.ID1Name2 Resident Table1;
NoConcatenate
Table5:
Load newID1 as ID1,Table2.ID1Name1, newDater as Dater Resident Table3;
Concatenate
Load newID2 as ID2,Table2.ID1Name2, newDater1 as Dater Resident Table4;
Drop Tables Table1,Table2,Table3,Table4;
Regards,
Prashant Sangle
Hi Prasanth,
Thank You! for understanding and giving your view.
Will try to implement and update the status.
Regards,
M. Parvez