Problem with joining two tables

[Anonymous]

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.

[jayanttibhe]

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

[Anonymous]

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.

[maxgro · Accepted solution]

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;

[Anonymous]

Thank you, It worked as I wanted