How to mapping multi values into Cartesian product?

[thomas_wang]

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!

[marcus_sommer]

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]

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.

[marcus_sommer]

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]

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.

[marcus_sommer]

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