Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
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.
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 |
Please post a sample of Data pointing keys to that join you tables
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 |
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;
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.
Hi Swuehl, Thanks for the response. The lookup table has more than 2 columns so I assume applymap will not work ?
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
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
];