Help with joining two tables

[sunny_talwar]

I have these two tables

Table1:

LOAD * INLINE [

    Type, ID1, ID2, Measure1

    S, 123, ABC, 20

    E, 456, DEF, 30

    F, 789, GHI, 40

];

Table2:

LOAD * INLINE [

    ID1, ID2, Measure2

    024, ABC, 0.7

    456, KLM, 0.3

    789, GHK, 0.4

];

I am looking to get this as an output

Type	ID1	ID2	Measure1	Measure2
S	123	ABC	20	0.7
E	456	DEF	30	0.3
F	789	GHI	40	0.4

Logic

The basic logic is for Type S join based on ID2 and for all other join based on ID1

[ElizaF]

Hi Sunny,

Another solution is to use applymap function.

Table1:

LOAD * INLINE [

    Type, ID1, ID2, Measure1

    S, 123, ABC, 20

    E, 456, DEF, 30

    F, 789, GHI, 40

];

Table1_MAP_1:

Mapping

LOAD

If(Type='S',ID2,ID1) AS KEY,

SUM(Measure1) as Measure1

Resident Table1

If(Type='S',ID2,ID1)

;

Table2:

LOAD

ApplyMap('Table1_MAP_1',ID1,0) + ApplyMap('Table1_MAP_1',ID2,0) AS Measure1,

*

;

LOAD * INLINE [

    ID1, ID2, Measure2

    024, ABC, 0.7

    456, KLM, 0.3

    789, GHK, 0.4

    050,CC,0.94

];

DROP table Table1;

[sunny_talwar]

Ruben -

Is this still an issue?

If Table 2 has a row with ID1=456 and ID2='ABC' it will join with Type 'S' because  ID2='ABC' in both tables and with Type 'E' because ID1=456 in both tables.

I am somehow unable to wrap my head around this. I want to make sure I understand this before I implement yours or mine solution.

Thanks,

Sunny

[sunny_talwar]

Thank you for your response elizafilip_2014‌. Your script is similar to Marcus, but way more complicated and has some errors. I was able to resolve the errors, but I think I am going to go with Ruben's method.

Thanks for your response once again.

Best,

Sunny

[rubenmarin]

It can be, I don't know if duplicate the row is the expected result:

Table1:

LOAD Type, ID1, ID2, Measure1, If(Type='S', 'S' & ID2, ID1) as Link Inline [

    Type, ID1, ID2, Measure1

    S, 123, ABC, 20

    E, 456, DEF, 30

    F, 789, GHI, 40

];

//Table2:

Left Join (Table1)

LOAD Subfield(ID1 &'#'& 'S' & ID2, '#') as Link, Measure2 Inline [

    ID1, ID2, Measure2

    024, ABC, 0.7

    456, KLM, 0.3

    789, GHK, 0.4

   456, ABC, 0.1

];

It will retun a duplicated 0.1 Measure2, One for type 'S' (same ID2) and another for Type 'E' (same ID1)