Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I need to join two tables in a specific way.
The first one contains, among other data, 8-digit account numbers:
40240000 |
40230100 |
40310000 |
40230000 |
419_____ |
411_____ |
70090192 |
where 40240000 is an example of a single account and "419_____" translates into all the accounts that begin with "419".
The second table contains all single account numbers, for example 40240000, 41901010, 41901020 and 41902010.
My output table should have all the single accounts from the second table joined together with corresponding data from the first table.
I have really no idea how to make one row form the first table split into appropriate number of rows for each fitting account from the second table.
Anyone got any idea? Thanks in advance
Regards.
try to add (bold) a new join field in the second table
FirstTable:
load * inline [
f1
40240000
40230100
40310000
40230000
419_____
411_____
70090192
];
tmp:
load left(f1, 3) as f3
Resident FirstTable
where right(f1, 5) = '_____';
SecondTable:
load
f2,
if(exists(f3, left(f2, 3)), left(f2,3)& '_____', f2) as f1
inline [
f2
40240000
41901010
41901020
41902010
];
//drop table tmp;
In the First table - lets say
Field1
40240000
40230100
40310000
40230000
419_____
411_____
70090192
in the second table where you have Single account numbers like
Field2
41901010
41901020
41101010
41101020
You can create new field like
Pick(wildmatch(Field2,'419*','411*',Field2),'419_____','411_____',Field2) as Field1
now Join on Field1 and If not needed then Delete the Field2 OR make sure it wont create Synthetic Key
The problem is that there are hundreds of accounts and they can change on a random basis. Is there any way to make it a fully automated process not requiring manual entering of the numbers into script?
Regards.
try to add (bold) a new join field in the second table
FirstTable:
load * inline [
f1
40240000
40230100
40310000
40230000
419_____
411_____
70090192
];
tmp:
load left(f1, 3) as f3
Resident FirstTable
where right(f1, 5) = '_____';
SecondTable:
load
f2,
if(exists(f3, left(f2, 3)), left(f2,3)& '_____', f2) as f1
inline [
f2
40240000
41901010
41901020
41902010
];
//drop table tmp;
Thank you, It worked as I wanted