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

[smarties]

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;

[rwunderlich]

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

[oskartoivonen]

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):

[smarties]

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

[MarcoWedel]

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