Search string inside loop taking huge time

[koushik_btech20]

I have 2 qvd , where in one qvd I have user role and Role entity data and in another qvd I have combined entity data , I need to link with combined entity and role , how this can be done, currently using below script,

ENTITY_AUTH:
LOAD
ROLE,
ENTITY_VAL
FROM [lib://qvd/SA/ENTITY_AUTH.qvd](qvd);

ENTITY_AUTH_VAL:
LOAD Distinct ENTITY_VAL Resident ENTITY_AUTH;

COMBINED_DISTINCT_ENTITY:
LOAD distinct CombinedEntity 

FROM [lib://qvd/SA/COMBINED_ENTITY.qvd](qvd);

ENTITY_VAL_LINK:
let MaxVal = NoOfRows('ENTITY_AUTH_VAL') -1;
for i = 0 to MaxVal
Let userauth = peek('ENTITY_VAL', $(i), 'ENTITY_AUTH_VAL');
LOAD CombinedEntity, TEXT('$(userauth)') as ENTITY_VAL resident COMBINED_DISTINCT_ENTITY
WHERE wildmatch(CombinedEntity, '$(userauth)') > 0;
next i

drop Table COMBINED_DISTINCT_ENTITY;
drop Table ENTITY_AUTH_VAL;

Above code taking more time as we have more than 1 million records in COMBINED_DISTINCT_ENTITY

Attached sample data for reference.

[marcus_sommer]

I'm not quite sure what you are trying to do but your approach must be extremely slow - because accessing around 1 million field-values from a table to load another larger table also 1 million times by applying a heavy where-clause ... 

Personally I would tend to use a mapping-approach. Either with resolved values by separating the combined values into n rows per subfield() or with a mapsubstring() and filtering then against the matches.

[koushik_btech20]

This script we use to establish the Section Access , where Role we use for data reduction , ROLE & ENTITY_VAL present in  ENTITY_AUTH which we have data in db and we are storing it in qvd as ENTITY_AUTH.qvd and Combined Entity is basically a composite key i.e. collaboration of multiple fact dataset which  that I need to link with ENTITY_VAL  in order to establish the section access , section access we have ACCESS,USERID & ROLE. Now through mapsubstirng() how it can be possible? 

[marcus_sommer]

Meant was to use something like:

m: mapping load Lookup, Lookup & '< TRUE >' as Return from X;

t:
load * where Match = 'TRUE';
load
   CallValue,
   coalesce(textbetween(mapsubstring('m', CallValue), '<', '>'), 'FALSE') as Match
from Y;

not primarily to replace sub-strings directly else to add an information - either any True/False matching and/or further data - which are uniquely extractable and usable as filters or for other transformations.

AFAIK it's the only possibility to execute a performant wildcard-join between data-sets in Qlik. Logically simpler is to perform a cartesian join between the data-sets and applying in the result the matching - but the needed resources will be definitely higher.

Your above loop-approach follows a classical programming logic and will there working quite well with I/O against array-variables. You could get a similar behaviour within Qlik if the logic is applied within a load-statement but not by looping from the outside and initializing n loads in the iteration (it's simply too much overhead).

Beside this I'm not sure that the above logic is really necessary. I could imagine that a match against single values is simpler and more performant and after that may come a field-combining and/or string-aggregation to address more complex access rights.