topic Applymap or Join with wildcards in QlikView

Applymap or Join with wildcards

Igor4 — Sat, 12 Apr 2025 06:51:11 GMT

Good morning,

I hope somebody can hel me.

I have one table inline like this:

OrdenPrueba:
LOAD INLINE [
Orden, Prueba
34281, Prueba
34457, Prueba
2400104, Prueba
2403224, Prueba
2501056, Prueba
2501041, Prueba
];

And I have other table where into the Id field can appears or not like part of it the filed Order of OrdenPrueba:

Numbers:

LOAD INLINE [

37464342817456,

2403224464747,

7465752501056,

36474757,

383993300,

];

I would like to insert a column in the table Numbers, where appears Prueba when one of the orders appears as part of the field Id

Thanks in advance

Re: Applymap or Join with wildcards

rubenmarin — Sat, 12 Apr 2025 07:49:55 GMT

Hi, if there are not too many numbers and orders, you can crete a flag as:

Cartesian: NoConcatenate LOAD * Resident OrdenPrueba; Outer Join (Cartesian) LOAD * Resident Numbers; Left Join (Cartesian) LOAD Id, 'Found' as Result, Id as chkIdFound Resident Cartesian Where Index(Id,Orden) ; Left Join (Numbers) LOAD Id, If(Exists('chkIdFound',Id),1,0) as Found Resident Numbers; DROP Table Cartesian;

Re: Applymap or Join with wildcards

rwunderlich — Sat, 12 Apr 2025 15:11:18 GMT

I like to use a script pattern I call "Wildcard Mapping". You can find documented downloadable examples for QS and QV int the Recipes section here:

Qlikview Cookbook: Mapping With Wildcards https://qlikviewcookbook.com/recipes/download-info/mapping-with-wildcards/

Using the example, your code might look like:

OrdenPrueba:
LOAD *
INLINE [
Orden, Prueba
34281, Prueba
34457, Prueba
2400104, Prueba
2403224, Prueba
2501056, Prueba
2501041, Prueba
];

_MapExpr:
LOAD
'pick(wildMatch($1,' & chr(39)
& concat(Key, chr(39) & ',' & chr(39), FieldIndex('Key', Key))
& chr(39)
& '), ' & chr(39)
& concat(Label, chr(39) & ',' & chr(39), FieldIndex('Key', Key))
& chr(39) & ')' as MapExpr
;
LOAD '*' & Orden & '*' as Key,
Prueba as Label
Resident OrdenPrueba
;

LET vMapExpr = peek('MapExpr', -1);
DROP TABLE _MapExpr;

Numbers:
LOAD *,
$(vMapExpr(Id)) as PruebaColumn
INLINE [
Id
37464342817456
2403224464747
7465752501056
36474757
383993300
];

-Rob

Re: Applymap or Join with wildcards

Igor4 — Tue, 22 Apr 2025 12:13:17 GMT

Sorry the delay.

The example works perfectly,.

I have tested with a table of 300.000 registers and there is not delay

Many thanks

Re: Applymap or Join with wildcards

Igor4 — Tue, 22 Apr 2025 12:13:39 GMT

Sorry the delay.

The example works perfectly,.

I have tested with a table of 300.000 registers and there is not delay

Many thanks

Re: Applymap or Join with wildcards

MarcoWedel — Sat, 24 May 2025 11:52:59 GMT

another solution might be

OrdenPrueba: LOAD * INLINE [ Orden, Prueba 34281, Prueba 34457, Prueba 2400104, Prueba 2403224, Prueba 2501056, Prueba 2501041, Prueba ]; MapPrueba: Mapping LOAD Orden, '@MapStart@'&Prueba&'@MapEnd@' Resident OrdenPrueba; Numbers: LOAD *, TextBetween(MapSubString('MapPrueba',Id),'@MapStart@','@MapEnd@') as TipoOrden INLINE [ Id 37464342817456 2403224464747 7465752501056 36474757 383993300 ];

hope this helps

Marco

Re: Applymap or Join with wildcards

Igor4 — Sun, 25 May 2025 06:50:54 GMT

Hi Marco,

It works too.

Many thanks