Applymap or Join with wildcards

[Igor4]

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 [

Id

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

[rubenmarin]

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;

[rwunderlich · Accepted solution]

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

[Igor4 · Accepted solution]

Sorry the delay.

The example works perfectly,.

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

Many thanks

[Igor4]

Sorry the delay.

The example works perfectly,.

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

Many thanks

[MarcoWedel · Accepted solution]

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

[Igor4]

Hi Marco,

It works too.

Many thanks