Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
the title might be confusing but here is the idea:
I have 2 tables and I want to join both tables, but....
here is the case:
table1:
Code | Description |
---|---|
101.110.255 | Desc1 |
101.100.202 | Desc2 |
101.200.301 | Desc3 |
202.110.111 | Desc4 |
202.100.200 | Desc5 |
302.300.100 | Desc6 |
table2:
CodeGroup | GroupName |
---|---|
101.1 | Current Asset |
101.200 | Fixed Asset |
202.11 | Income |
202.10 | Other Income |
302.3 | Other Income |
The expected result is:
Code | Description | CodeGroup | GroupName |
---|---|---|---|
101.110.255 | Desc1 | 101.1 | Current Asset |
101.100.202 | Desc2 | 101.1 | Current Asset |
101.200.301 | Desc3 | 101.200 | Fixed Asset |
202.110.111 | Desc4 | 202.11 | Income |
202.100.200 | Desc5 | 202.10 | Other Income |
302.300.100 | Desc6 | 302.3 | Other Income |
I want to show which code belong to which CodeGroup and GroupName.
Thanks for your help
Hi Reynald,
You can build an intermediate lookup table to join the two tables as this ...
Lkp:
LOAD Code, text(left(Code,5)) as Key resident Table1;
LOAD Code, text(left(Code,6)) as Key resident Table1;
LOAD Code, text(left(Code,7)) as Key resident Table1;
inner join (Lkp)
LOAD text(CodeGroup) as Key, GroupName resident Table2;
Note the text statement around the Code field as the CodeGroup values are numeric whereas the Codes are not.
flipside
Hi,
If that are the only data you have to join, you could create two fields in both tables with the first 5,6 characters so you will have the Keys to join the tables.
Table1:
Load *,
Left(Code,5) as %Key,
Left(Code,6) as %Key2
From TABLE1;
Left join(Table1)
Load *,
if(len(CodeGroup)=5,Left(CodeGroup,5)) as %Key
From TABLE2;
Left join(Table1)
Load *,
if(len(CodeGroup)>5,Left(CodeGroup,6)) as %Key2
From TABLE2;
I ' ve don't try this, hope this helps
Hi Reynald,
You can build an intermediate lookup table to join the two tables as this ...
Lkp:
LOAD Code, text(left(Code,5)) as Key resident Table1;
LOAD Code, text(left(Code,6)) as Key resident Table1;
LOAD Code, text(left(Code,7)) as Key resident Table1;
inner join (Lkp)
LOAD text(CodeGroup) as Key, GroupName resident Table2;
Note the text statement around the Code field as the CodeGroup values are numeric whereas the Codes are not.
flipside
Hi, thanks for ur help btw.
I havent tried any of the solution
but once Ive tried, i will inform my progress.
Thanks anyway
Thanks flipside,
Your solution worked like charm.
Thank you so much.