Qlik Community

QlikView Security & Governance

Discussion Board for collaboration on QlikView Security and Governance.

Highlighted
thomas_wang
Contributor

How to mapping multi values into Cartesian product?

As we all known, ApplyMap() function can only mapping the first met value. For example, I define the following Mapping table.


MAPPING_A:

Mapping LOAD * Inline [
A, Mapping
a1, 001
a1, 002
a2, 003
]
;


And I already have the following fact table.

FACT_A:
LOAD * Inline [
A
a1
a2
]
;

If I execute the following script:

LOAD ApplyMap('MAPPING_A', A, 'Others') as MAPPED_A Resident FACT_A;

Then I can only get the following data.


MAPPED_A

001

003


Actually, I want the following data.

MAPPED_A

001

002

003

Behavior similar to SubField() function.

LOAD If(A = 'a1', SubField('001|002', '|'), ApplyMap('MAPPING_A', A, 'Others')) as MAPPED_A Resident FACT_A;

Is there a better way to mapping data without converting them to strings?

Thanks!

5 Replies
MVP & Luminary
MVP & Luminary

Re: How to mapping multi values into Cartesian product?

You could try it in this way:

MAPPING_A:

Mapping LOAD A, concat(Mapping, '|') as Mapping Inline [
A, Mapping
a1, 001
a1, 002
a2, 003
] group by A
;


FACT_A:
LOAD * Inline [
A
a1
a2
]
;

load subfield(MAPPED_A, '|') as MAPPED_A;

LOAD ApplyMap('MAPPING_A', A, 'Others') as MAPPED_A

Resident FACT_A;

But do you want really to create additionally records within your facts?

- Marcus

thomas_wang
Contributor

Re: How to mapping multi values into Cartesian product?

Yes, this is very similar to the way I'm trying. But I think it's troublesome, and I worry about the conversion process of strings. For example, I can't guarantee that the data do not contain separator “|”.

Thanks and regards.

MVP & Luminary
MVP & Luminary

Re: How to mapping multi values into Cartesian product?

Instead of '|' you could also us any other char which is quite unlikely within the data - maybe chr(5000) or even one of control-chars like chr(1).

- Marcus

thomas_wang
Contributor

Re: How to mapping multi values into Cartesian product?

I suddenly thought of join tables. An unidirectional external join to the fact table with mapping table, then drop the key field. Is there any risk of doing JOIN in scripts? Such as performance, resource occupancy and program robustness?

Thanks and regards.

MVP & Luminary
MVP & Luminary

Re: How to mapping multi values into Cartesian product?

In general you could also use a join to duplicate the records. By applying a "classical" join no duplicating or removing of records is aimed else just adding new fields to a table - in such use-cases a mapping-approach is often significantly faster as a join and has furthermore no risk of changing the number of records.

But by creating additionally records I'm not sure if this is true in the same way and I think it will depend on your other requirements which way is more suitable as the another.

- Marcus