topic Re: Merge / Join two resident tables in App Development

Merge / Join two resident tables

klikgevoel — Sat, 16 Nov 2024 20:53:50 GMT

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

Re: Merge / Join two resident tables

Frank_Hartmann — Fri, 10 May 2019 13:59:53 GMT

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;

Re: Merge / Join two resident tables

klikgevoel — Wed, 15 May 2019 12:46:28 GMT

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 🙂