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

[sunny_talwar]

This seems to be working, but I would still be interested in knowing other solutions

Table1:

LOAD *,

  If(Type = 'S', Type&'-'&ID2, ID1) as Link;

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

];

FinalTable2:

LOAD ID1 as Link,

  Measure2

Resident Table2;

Concatenate (FinalTable2)

LOAD 'S-' & ID2 as Link,

  Measure2

Resident Table2;

Left Join (Table1)

LOAD Link,

  Measure2

Resident FinalTable2;

DROP Table FinalTable2, Table2;

[rubenmarin]

Hi Sunny, my script (maybe it can't work with real data):

Table1:

LOAD Type, ID1, ID2, Measure1, If(Type='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 &'#'& ID2, '#') as Link, Measure2 INLINE [

    ID1, ID2, Measure2

    024, ABC, 0.7

    456, KLM, 0.3

    789, GHK, 0.4

];

And a question: What if Table 2 has a row with ID1=456 and ID2='ABC'? Can this happen?

[sunny_talwar]

I will try this out. And to answer your question, yes, ID1 = 456 and ID2 = 'ABC' can happen in a single row.

[marcus_sommer]

I have a different approach which at least work with the few sample-data:

Table2:

crosstable(ID, IDValue, 1)

LOAD Measure2, ID1, ID2 INLINE [

    ID1, ID2, Measure2

    024, ABC, 0.7

    456, KLM, 0.3

    789, GHK, 0.4

];

map:

mapping load IDValue, Measure2 resident Table2;

Table1:

LOAD *,

  applymap('map', If(Type = 'S', ID2, ID1), '#NV') as Measure2;

LOAD * INLINE [

    Type, ID1, ID2, Measure1

    S, 123, ABC, 20

    E, 456, DEF, 30

    F, 789, GHI, 40

];

drop table Table2;

- Marcus

[rubenmarin]

And what it should do? Join with Type 'E' and Type 'S' of Table1 or some Type has preference?

[sunny_talwar]

I may have incorrectly hinted that ID1 is always numeric and ID2 is always text. They can (very rarely) match also. For instance, like below

Table1:

LOAD * INLINE [

    Type, ID1, ID2, Measure1

    S, 123, ABC, 20

    E, 456, DEF, 30

    F, 789, GHI, 40

    S, 352, 555, 34

    E, 555, KTP, 43

];

[sunny_talwar]

So if Type = S in table1 then join explicitly on ID2 and for all other Types join on ID1. Does that answer your question? I might not have fully understood your question

[rubenmarin · Accepted solution]

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.

To solve having numbers in ID2 and numbers in ID1 (copied from your idea to add an 'S' 😞

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

];

[sunny_talwar]

This is essentially doing the same thing, but in a much more smaller script. Thanks Ruben!!!

Best,

Sunny