topic Problem with joining two tables in QlikView

Problem with joining two tables

Anonymous — Wed, 10 Oct 2018 09:24:37 GMT

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.

Re: Problem with joining two tables

jayanttibhe — Wed, 10 Oct 2018 15:25:30 GMT

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

Re: Problem with joining two tables

Anonymous — Thu, 11 Oct 2018 07:50:31 GMT

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.

Re: Problem with joining two tables

maxgro — Thu, 11 Oct 2018 18:39:40 GMT

try to add (bold) a new join field in the second table

FirstTable:

load * inline [

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 [

40240000

41901010

41901020

41902010

];

//drop table tmp;

Re: Problem with joining two tables

Anonymous — Wed, 17 Oct 2018 11:14:33 GMT

Thank you, It worked as I wanted