multiple join

[entsh]

hello

would you please help me:

table1

date	num
12/12/2022	9
12/13/2022	20
12/14/2022	21
12/15/2022	56
12/16/2022	20
12/17/2022	10
12/18/2022	11

table2:

date1	date2	date3
12/15/2022	12/13/2022	12/12/2022
12/16/2022	12/14/2022	12/13/2022
12/17/2022	12/15/2022	12/16/2022
12/18/2022	12/18/2022	12/17/2022

I want  to compare table 1 and table 2 (column date with columns date1,date2,date3) and add & fill num1,num2,num3( red columns)

result:

date1	num1	date2	num2	date3	num3
12/15/2022	56	12/13/2022	20	12/12/2022	9
12/16/2022	20	12/14/2022	21	12/13/2022	20
12/17/2022	10	12/15/2022	56	12/16/2022	20
12/18/2022	11	12/18/2022	11	12/17/2022	10

what should i write in edit script?

thanks

[stevejoyce]

assuming table 1 and table 2 loaded already.

left join (table2)

load 

  date as date1

  , num as num1

resident table1;

left join (table2)

load 

  date as date2

  , num as num2

resident table table1;

left join (table2)

load 

  date as date3

  , num as num3

resident table1;

drop table table1;

[MayilVahanan · Accepted solution]

Hi

Try like below

MapNum:

Mapping Load date, num from table1;

Load date1, applymap('MapNum', date1, 0) as num1,

date2, applymap('MapNum', date2, 0) as num2,

date3, applymap('MapNum', date3, 0) as num3

from table2;

Hope it helps