topic Re: Slow to load code that implements Exists() with a wildcard in App Development

Slow to load code that implements Exists() with a wildcard

smarties — Fri, 02 Sep 2022 19:08:01 GMT

Hi,

I have some code that is functionally correct, but it's slow to load - 5 mins. The code is an attempt to provide the functionality of Exists() with a wildcard. Any thoughts on how to speed-up with a different approach would be appreciated.

The field list 'TargetDevice' is some tens of parts. The field list 'RUPI' is 25k parts. A 'TargetDevice' is the front portion of a 'RUPI'. How much is an unknown, hence the need for the * wildcard. The desired outcome is a table that maps 'TargetDevice' to 'RUPI'.

Thanks.

Code that works, but is slow to load ...

Mapping:
Load *
Inline
[
    TargetDevice, RUPI
];

For Each vTargetDevice in FieldValueList('TargetDevice')

    Let vFound = False();

    For Each vRUPI in FieldValueList('RUPI')

        If '$(vRUPI)' Like '$(vTargetDevice)*' Then

            Concatenate(Mapping)
            Load *
            Inline
            [
                TargetDevice, RUPI
                $(vTargetDevice), $(vRUPI)
            ];

            Let vFound = True();

        End If;

    Next vRUPI;

    If not vFound Then

        Concatenate(Mapping)
        Load *
        Inline
        [
            TargetDevice, RUPI
            $(vTargetDevice), NotFound
        ];

    End If;

Next vTargetDevice;

Re: Slow to load code that implements Exists() with a wildcard

rwunderlich — Fri, 02 Sep 2022 20:28:04 GMT

I would use a "wildcard mapping table" approach. See this downloadable example.

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

For your case, it would look something like this:

wildmap:
LOAD
TargetDevice as Key,
'*' & TargetDevice as Label
from ....;

_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
RESIDENT wildmap
;
LET vMapExpr = peek('MapExpr', -1);
DROP TABLE _MapExpr;
DROP TABLE wildmap;

Facts:
LOAD
$(vMapExpr(RUPI)) as TargetDevice,
...
from ...;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Re: Slow to load code that implements Exists() with a wildcard

oskartoivonen — Sun, 04 Sep 2022 21:37:26 GMT

Looping through each TargetDevice and RUPI combination is probably the slowest approach in Qlik. You want to load the RUPI list once with a LOAD-statement, and check each RUPI for Target Device hits with a search.

Maybe this script does what you need? It looks a bit wonky because we're using dynamic script, but should execute over a 25k table swiftly. The Test-field can be used directly in the WHERE statement to filter the rows with no hits.

TargetDevice: LOAD * INLINE [ TargetDevice A B C ]; let vL.WildMatch = ; for each vL.TargetDevice in FieldValueList('TargetDevice') if Len('$(vL.WildMatch)') = 0 then let vL.WildMatch = '''$(vL.TargetDevice)*'''; else let vL.WildMatch = '$(vL.WildMatch), ''$(vL.TargetDevice)*'''; endif next Test: LOAD RUPI, WildMatch(RUPI, $(vL.WildMatch)) as Test, Pick(WildMatch(RUPI, $(vL.WildMatch)), $(vL.WildMatch) ) as Test2, Replace(Pick(WildMatch(RUPI, $(vL.WildMatch)), $(vL.WildMatch) ), '*', '') as Test3 INLINE [ RUPI A100 B200 C300 D400 ];

Result (Test3 is what you want):

Re: Slow to load code that implements Exists() with a wildcard

smarties — Fri, 09 Sep 2022 18:32:48 GMT

Thank you @rwunderlich and @oskartoivonen. It's taken me a while to get my head round what you've provided. New techniques learnt. 😊

Re: Slow to load code that implements Exists() with a wildcard

MarcoWedel — Sat, 10 Sep 2022 14:27:17 GMT

maybe another solution could be:

// some random test data tabTargetDevice: LOAD Left(KeepChar(Hash128(Rand()),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),Ceil(Rand()*3)+1) as TargetDevice AutoGenerate 1000; tabRUPI: LOAD Left(KeepChar(Hash256(Rand()),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),Ceil(Rand()*8)+4) as RUPI AutoGenerate 100000; // Mapping mapTargetDevice: Mapping LOAD Distinct TargetDevice, '@FoundOne@'&TargetDevice&'@RightUpToHere@' Resident tabTargetDevice; tabMapping: LOAD Distinct RUPI, TextBetween(RUPIMapped,'@FoundOne@','@RightUpToHere@',1) as TargetDevice Where RUPIMapped like '@FoundOne@*'; LOAD RUPI, MapSubString('mapTargetDevice',RUPI) as RUPIMapped Resident tabRUPI;

hope this helps

Marco