Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Need help with the joining tables issue as below:
Issue is that the date values are being shown on separate rows
I tried to use len(trim(Concat(distinct child2_solddate))) , but this is not working
Table1:
parentID | childID | child1_Startdate | sta |
p1 | t1 | 5-Nov-18 | 1 |
p1 | t2 | 6-Nov-18 | 1 |
p1 | t3 | 7-Nov-18 | 1 |
p4 | t4 | 8-Nov-18 | 1 |
p5 | t5 | 9-Nov-18 | 1 |
p5 | t6 | 10-Nov-18 | 1 |
p7 | t7 | 11-Nov-18 | 1 |
p8 | t8 | 12-Nov-18 | 1 |
Table2:
parentID | childID | child2_Enddate1 | child2_filldate | child2_solddate | sta |
p1 | t9 | 10-Oct-18 | 20-Sep-18 | 25-Oct-18 | 4 |
p2 | t10 | 11-Oct-18 | 21-Sep-18 | 26-Oct-18 | 4 |
p3 | t11 | 12-Oct-18 | 22-Sep-18 | 27-Oct-18 | 4 |
p4 | t12 | 13-Oct-18 | 23-Sep-18 | 28-Oct-18 | 4 |
p5 | t13 | 14-Oct-18 | 24-Sep-18 | 29-Oct-18 | 4 |
p6 | t14 | 15-Oct-18 | 25-Sep-18 | 30-Oct-18 | 4 |
p7 | t15 | 16-Oct-18 | 26-Sep-18 | 31-Oct-18 | 4 |
p8 | t16 | 17-Oct-18 | 27-Sep-18 | 1-Nov-18 | 4 |
Required result:
parent | child | sta | child1_Startdate | child2_Enddate1 | child2_filldate | child2_solddate |
p1 | t1 | 1 | 5-Nov-18 | 10-Oct-18 | 20-Sep-18 | 25-Oct-18 |
p1 | t2 | 1 | 6-Nov-18 | 10-Oct-18 | 20-Sep-18 | 25-Oct-18 |
p1 | t3 | 1 | 7-Nov-18 | 10-Oct-18 | 20-Sep-18 | 25-Oct-18 |
p4 | t4 | 1 | 8-Nov-18 | 13-Oct-18 | 23-Sep-18 | 28-Oct-18 |
p5 | t5 | 1 | 9-Nov-18 | 14-Oct-18 | 24-Sep-18 | 29-Oct-18 |
p5 | t6 | 1 | 10-Nov-18 | 14-Oct-18 | 24-Sep-18 | 29-Oct-18 |
p7 | t7 | 1 | 11-Nov-18 | 16-Oct-18 | 26-Sep-18 | 31-Oct-18 |
p8 | t8 | 1 | 12-Nov-18 | 17-Oct-18 | 27-Sep-18 | 1-Nov-18 |
You need to load only parentID, child2_Enddate1, child2_filldate and child2_solddate from table2:
MyTable:
LOAD * FROM Table1;
LEFT JOIN (MyTable)
LOAD parentID, child2_Enddate1, child2_filldate, child2_solddate FROM Table2;
That way only the parentID field will be used to join the records.
Hi,
Final_Table:
Load PatrentID as parent, ChildID as Child, child1_Startdate, sta from Table1;
left join(Final_Table)
load ParentID as Parent, child2_Enddate1, child2_filldate, child2_solddate from table2;