Consecutive join

[#1]

Hi

I have a table T1: that has 3 columns ID1, ID2, Description and i have a file with columns ID, DATE, VALUE,  NEXTV. I need to join the two tables but ID field in the file can either be ID1 OR ID2 in T1.

I therefore  did a join (T1) ...... where exists (ID1, ID) and another join (T1)..... where exists (ID2, ID) but i understand this isnt working because its trying to do the 2nd join on the other similar fields, DATE, VALUE, NEXTV . A concatenate creates duplicates of ID2 in the resulting T1 with null values in ID1.

Any ideas how to get round this? Thankd

[Gysbert_Wassenaar]

Create two temporary tables. The first will have only the ID1 values fromT1 and no other fields. The other only the ID2 values from T1 and no other fields. Join the two other tables to these tables. Then join these two temporary tables to the original T1 table.

There may be easier ways, but I don't know your data well enough. Perhaps you can post some example source files.

[#3]

Thanks Gysbert. This is what the data looks like from Table 1 and the input file and the last is the result I want

Table 1
ID1	ID2	Desc	other
ChildIDa	ParentIDa	50mm	wide
ChildIDb	ParentIDb	60mm	narrow
ChildIDc	ParentIDc	70mm	wide
ChildIDd	ParentIDd	80mm	narrow
ChildIDe	ParentIDe	90mm	wide
Input File
ID	Date	Read	Loc
ChildIDa	5/02/2015	13	LA
ChildIDb	6/02/2015	14	WA
ParentIDc	7/02/2015	14	NSW
ParentIDd	8/02/2015	16	QLD
ParentIDe	9/02/2015	17	LA
Result I want
ID1	ID2	Desc	other	Date	Read	Loc
ChildIDa	ParentIDa	50mm	wide	5/02/2015	13	LA
ChildIDb	ParentIDb	60mm	narrow	6/02/2015	14	WA
ChildIDc	ParentIDc	70mm	wide	7/02/2015	14	NSW
ChildIDd	ParentIDd	80mm	narrow	8/02/2015	16	QLD
ChildIDe	ParentIDe	90mm	wide	9/02/2015	17	LA

[ziadm]

Please post a sample of Data pointing keys to that join you tables

[#5]

Thanks Ziad

Table 1
ID1	ID2	Desc	other
ChildIDa	ParentIDa	50mm	wide
ChildIDb	ParentIDb	60mm	narrow
ChildIDc	ParentIDc	70mm	wide
ChildIDd	ParentIDd	80mm	narrow
ChildIDe	ParentIDe	90mm	wide
Input File
ID	Date	Read	Loc
ChildIDa	5/02/2015	13	LA
ChildIDb	6/02/2015	14	WA
ParentIDc	7/02/2015	14	NSW
ParentIDd	8/02/2015	16	QLD
ParentIDe	9/02/2015	17	LA
Result I want
ID1	ID2	Desc	other	Date	Read	Loc
ChildIDa	ParentIDa	50mm	wide	5/02/2015	13	LA
ChildIDb	ParentIDb	60mm	narrow	6/02/2015	14	WA
ChildIDc	ParentIDc	70mm	wide	7/02/2015	14	NSW
ChildIDd	ParentIDd	80mm	narrow	8/02/2015	16	QLD
ChildIDe	ParentIDe	90mm	wide	9/02/2015	17	LA

[swuehl]

Another solution could be (not really sure without knowing your data), assuming ID1 and ID2 are primary keys also when looked at the union:

MAP:

MAPPING LOAD ID1 as Key, Description

FROM T1TableSource;

MAPPING LOAD ID2 as Key, Description

FROM T1TableSource;

T2:

LOAD ID, DATE, VALUE, NEXTV,

          ApplyMap('MAP', ID, 'no description map found') as DESCRIPTION

FROM T2TableSource;

[swuehl]

You should be able to use the MAPPING approach I've shown in my previous answer, maybe repeat the above for your two fields to map, Desc, other.

[#8]

Hi Swuehl, Thanks for the response. The lookup table has more than 2 columns so I assume applymap will not work ?

[swuehl]

A Mapping table can only have 2 columns, one key column, one value column that is returning the value to map to.

But you can either

- use two mapping tables, one for description, one for other (that's what I indicated in my previous post)

- use a combinated value column, a column that contains both source columns concatenated. Then split the returned value into parts.

I would probably go for the first option, it's just easier to maintain

[Gysbert_Wassenaar · Accepted solution]

Solution one:

Table1:
LOAD * INLINE [
    ID1, ID2, Desc, other
    ChildIDa, ParentIDa, 50mm, wide
    ChildIDb, ParentIDb, 60mm, narrow
    ChildIDc, ParentIDc, 70mm, wide
    ChildIDd, ParentIDd, 80mm, narrow
    ChildIDe, ParentIDe, 90mm, wide
];

InputFile:
LOAD * INLINE [
    ID, Date, Read, Loc
    ChildIDa, 5/02/2015, 13, LA
    ChildIDb, 6/02/2015, 14, WA
    ParentIDc, 7/02/2015, 14, NSW
    ParentIDd, 8/02/2015, 16, QLD
    ParentIDe, 9/02/2015, 17, LA
];

T1:
LOAD ID as ID1, Date, Read, Loc RESIDENT InputFile WHERE Exists(ID1, ID);

INNER JOIN (T1)

LOAD * RESIDENT Table1;

T2:
LOAD ID as ID2, Date, Read, Loc RESIDENT InputFile WHERE Exists(ID2, ID);

INNER JOIN (T2)

LOAD * RESIDENT Table1;

CONCATENATE(T1)

LOAD * RESIDENT T2;

DROP TABLE Table1, InputFile, T2;

Solution two:

mapInputFile:
MAPPING 
LOAD 
  ID, 
  Date&'|'&Read&'|'&Loc as Values 
INLINE [
    ID, Date, Read, Loc
    ChildIDa, 5/02/2015, 13, LA
    ChildIDb, 6/02/2015, 14, WA
    ParentIDc, 7/02/2015, 14, NSW
    ParentIDd, 8/02/2015, 16, QLD
    ParentIDe, 9/02/2015, 17, LA
];

Table1:
LOAD 
  *,
  SubField(List, '|',1) as Date,
  SubField(List, '|',2) as Read,
  SubField(List, '|',3) as Loc;
LOAD 
  *,
  applymap('mapInputFile',ID1,applymap('mapInputFile',ID2)) as List 
INLINE [
    ID1, ID2, Desc, other
    ChildIDa, ParentIDa, 50mm, wide
    ChildIDb, ParentIDb, 60mm, narrow
    ChildIDc, ParentIDc, 70mm, wide
    ChildIDd, ParentIDd, 80mm, narrow
    ChildIDe, ParentIDe, 90mm, wide
];