Map with multiple fields

luis_pimentel — Thu, 24 Nov 2016 09:15:11 GMT

Hi Community,

I am facing a challenge that I would like to share with everyone.

Imagine we have a FACT table like:

FACT:

A	B	C	D	E	F	G
1	8	9	3	4	10	1
9	2	7	2	2	2	6
3	3	1	4	4	6	6
4	4	2	3	7	5	6

And also a "Mapping" Table like:

A	B	C	D	E	F	G	OUTPUT
1			3	4			AA
	2					6	BB
	3			4			CC
4	4				5		DD

Now, I need to retrieve the OUTPUT values in the FACT table. Considering that "blank" values in the mapping table means "regardless the value in the fact table".

The only solution I can think about is using a LOOP to create "all possible combinations of keys" or "all possible mapping tables" since the key to join both table is unknown. In other words, The key might be , A, AB, ABC, ABCD, .... etc. That means we have 7 factorial = 5040 possible keys.

FYI, my real scenario consist in a table with +50k rows and 10 mapping fields (about 4M possible combinations of keys). And a fact table with +50M rows.

Hope the explanation is clear enough.

Any ideas?

Thank you.

topic Map with multiple fields in QlikView

Map with multiple fields