Merge / Join two resident tables

[klikgevoel]

Hi all,

Lets assume we have two arbitrary tables, each loaded independently. Both tables use the NoConcatenate statement and Resident load, because some data wrangling is happening. Yet, both tables do share one field name in common, which I want to use to concatenate / join one table to the other.

How can I merge the first table with the other using either a concatenate or join statement? I find it quite confusing figuring it out compared to other languages as Python, R etc. where I can easily specify the dataframes and the key to use to merge rows.

Some arbitrary data

raw_SBI:

Load Inline [

sbi, name

1, sbiname1

2, sbiname2

3, sbiname3

4, sbiname4 ];

SBI:

NoConcatenate

Load

sbi,

Left(name, '7') as sbi_name

Resident raw_SBI;

raw_names:

Load Inline[

sbi, section

1, section1

2, section2

3, section3

4, section4];

names:

NoConcatenate

Load

sbi,

Left(section, '7') as section_name

Resident raw_names;

So the end result will provide me a table like:

sbi, section_name, sbi_name

1, section, name

2, section, name

3, section, name

4, section, name

[Frank_Hartmann]

try this:

raw_SBI:
Load * Inline [
sbi, name
1, sbiname1
2, sbiname2
3, sbiname3
4, sbiname4 ]; 

SBI:
NoConcatenate
Load
sbi,
Left(name, '7') as sbi_name
Resident raw_SBI; 

raw_names:
Load * Inline [
sbi, section
1, section1
2, section2
3, section3
4, section4];

names:
left join (SBI)
Load
sbi,
Left(section, '7') as section_name
Resident raw_names; DROP Tables raw_names,raw_SBI;

[klikgevoel]

For some reason the last part of the code did not work at my end (i.e. names: left join (sbi) etc.) 

As a work around I simply used the statement left join (sbi), and used that very same name in another resident load. So after the left join the table is named sbi instead of names.

Thanks for your help anyway 🙂