joining tables issue

[qvqfqlik]

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

[Gysbert_Wassenaar]

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.

[muthukumar77]

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;