topic For Each Loop / Crosstable matching two tables in QlikView

For Each Loop / Crosstable matching two tables

Thu, 12 Jun 2014 09:25:29 GMT

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

Re: For Each Loop / Crosstable matching two tables

Thu, 12 Jun 2014 14:49:39 GMT

Is this really not possible?

Thanks!

Re: For Each Loop / Crosstable matching two tables

Jason_Michaelides — Thu, 12 Jun 2014 16:47:22 GMT

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

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

Re: For Each Loop / Crosstable matching two tables

Thu, 12 Jun 2014 16:52:57 GMT

There will be about 10...

Re: For Each Loop / Crosstable matching two tables

MarcoWedel — Sat, 14 Jun 2014 01:46:33 GMT

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

Re: For Each Loop / Crosstable matching two tables

MarcoWedel — Wed, 09 Jul 2014 12:39:58 GMT

Hi,

please close this thread if there are no further questions.

Thanks

Regards

Marco