Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Problem with joining two tables

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.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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;

1.png

View solution in original post

4 Replies
jayanttibhe
Creator III
Creator III

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
Not applicable
Author

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
MVP
MVP

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;

1.png

Anonymous
Not applicable
Author

Thank you, It worked as I wanted