Join/apply map/concate

[parvez933]

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  

[Vegar]

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? 

[parvez933]

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

[PrashantSangle · Accepted solution]

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

[parvez933]

Hi Prasanth,

Thank You! for understanding and giving your view.

Will try to implement and update the status.

Regards,

M. Parvez