For Each Loop / Crosstable matching two tables

[#1]

Hi, following situation with two tables:

Table 1 (Millions of Rows):

ID	dim1	dim2	dim3
1234	aaa	bbb	ccc
5678	aaa	eee	fff

Table 2  (Reasonable number of rows):

dim1	dim2	dim3	CID
aaa	-	ccc	1
aaa	bbb	ccc	2
-	eee	-	3
-	eee	fff	4
aaa	-	-	5

I want to generate a third table that matches those two up:

CID	ID
1	1234
2	1234
3	5678
4	5678
5	1234
5	5678

I think crosstable might work, but my issue is that if a dim in Table 2 is empty, that one should be ignored and ANY value in Table 1 is fine. As you can see in my example table, duplicate values in both columns are supposed to happen. Any ID can be in multiple CIDs and any CID can have multiple IDs.

Any idea how I could get this to work please?

Thanks!
Oli

[#2]

Is this really not possible?

Thanks!

[Jason_Michaelides]

It's possible but might be a tad complicated...

Have you only got 3 dims or may there be many more?

[#4]

There will be about 10...

[MarcoWedel]

Hi Oli,

one solution could be:

SET CustomMatch = if(len($1) > 0 AND len($2) > 0, $1=$2, -1); 

tab1: 
LOAD *
FROM [http://community.qlik.com/thread/121748] (html, codepage is 1252, embedded labels, table is @1);

tab2: 
LOAD CID,
    PurgeChar(dim1, '-') as dim1_2,
    PurgeChar(dim2, '-')  as dim2_2,
    PurgeChar(dim3, '-')  as dim3_2
FROM [http://community.qlik.com/thread/121748] (html, codepage is 1252, embedded labels, table is @2);

NoConcatenate 
tabTemp: 
LOAD Distinct * Resident tab1; 
Join 
LOAD Distinct * Resident tab2; 

DROP Table tab2;

NoConcatenate 
tabMatches: 
LOAD * Resident tabTemp 
Where 
  $(CustomMatch(dim1, dim1_2)) AND 
  $(CustomMatch(dim2, dim2_2)) AND 
  $(CustomMatch(dim3, dim3_2)); 

DROP Table tabTemp; 

Left Join (tab1) 
LOAD Distinct 
  dim1, dim2, dim3, CID 
Resident tabMatches; 

DROP Table tabMatches;

one issue with this solution could be the first join of tab1 and tab2, which might use to much RAM with your big tables.

hope this helps nevertheless

regards

Marco

[MarcoWedel]

Hi,

please close this thread if there are no further questions.

Thanks

Regards

Marco