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_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
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.
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?
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.
