Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Is this really not possible?
Thanks!
It's possible but might be a tad complicated...
Have you only got 3 dims or may there be many more?
There will be about 10...
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
Hi,
please close this thread if there are no further questions.
Thanks
Regards
Marco