topic Re: Bad performance using variable to calculate field in Connectivity & Data Prep

Bad performance using variable to calculate field

pedrohenriqueperna — Tue, 19 Dec 2023 01:34:32 GMT

Hi,

I'm doing some extense transformation in customer data and at some point I compare address strings looking for typing errors. Things were working fine before my last step that involves using a big nested Replace() expression, along with some SubStringCount() and SubField().

Basically, instead of repeating this big expression I added it to a SET variable and surprisingly it works, but only if I calculate the variable without single quotes -> $(vExample) as MyField instead of '$(vExample)' as MyField.

The problem is that loading is now taking too long, and the weird thing about it is that I only use this in a first table load that prepares the data to a nested loop sequence, but it affects directly the rest of the script.

Why is this happening? The table with the variable as a field is already loaded and it loaded fast. Is it because I'm using the values from the table and it somehow recalculates everytime?

Re: Bad performance using variable to calculate field

pedrohenriqueperna — Tue, 19 Dec 2023 01:39:29 GMT

SET vReplaceRuaODS = If(IsNum(SubField(

$(vReplaceFunc)Upper(cliente_rua_temp),
$(vReplaceStr), ' ', SubStringCount(

$(vReplaceFunc)Upper(cliente_rua_temp),
$(vReplaceStr), ' ') + 1)), Trim(PurgeChar(

$(vReplaceFunc)Upper(cliente_rua_temp),
$(vReplaceStr), '1234567890')),

Trim($(vReplaceFunc)Upper(cliente_rua_temp),
$(vReplaceStr)));

This is the variable holding the expression as text

Re: Bad performance using variable to calculate field

marcus_sommer — Tue, 19 Dec 2023 10:08:26 GMT

Your variable contained itself variables which is technically possible but not always trivial or expedient (if the complexity increased more as it simplified the matter). Beside this looked the call of:

...

SubField($(vReplaceFunc)Upper(cliente_rua_temp),
...

within the outer-variable not correct because the variable here is isolated ...

As an alternatively for multiple (nested) replace-statement you may apply a mapsubstring().

Re: Bad performance using variable to calculate field

pedrohenriqueperna — Tue, 19 Dec 2023 13:39:29 GMT

I've looked into mapsubstring() but the example in qlik help got me a bit confused.

Considering the following nested replace expression, can you give me an example of how it would work?

Replace(Replace(Replace(Replace(customer_address, 'Avenida ', ''), 'Estrada ', ''), 'Rodovia ', ''), 'Alameda ', '');

In this case I'm trying to remove these substrings (ex. avenida, estrada..) and replacing it for nothing. I ended up using replace because I can get the whole substring with a subsequent space ('Avenida ') to make sure to replace only if it's the first substring inside the string.

As for what you said about the variable being isolated, I'm not sure if I understood it right, but if it's about having the Upper(..) right beside the variable, it's because I added the nested Replace(Replace(Replace( to the vReplaceFunc, and the to be replaced strings (ex. 'Avenida ', 'Estrada '...) to the vReplaceStr. Finally, to avoid having a different variable to each field I need to replace I used Upper(field) between the variables. I know it's messy, but it does work. Unfortunately it affects the performance of the script overall

Re: Bad performance using variable to calculate field

marcus_sommer — Tue, 19 Dec 2023 14:12:53 GMT

The mapsubstring() might be applied like:

m: mapping load * inline [
Lookup, Return
"Avenida ", ""
"Estrada ", ""
"Rodovia ", ""
"Alameda ", ""];

t: load *, mapsubstring('m', YourField) as YourFieldAdjusted
resident X;

Considering spaces within an inline-load is sometimes a bit tricky but mostly a wrapping with quotes will do the job. Alternatively the verbatim-variable might be adjusted. But nearly always it will be to load such data from an external source which will also enable other users to add/remove/change mapping-values which have no access to the script.

Each kind of wrong typing could be added to the mapping and the mappings might be also hierarchically nested like:

mapsubstring('m2', mapsubstring('m1', YourField))

In regard to your variable must be something quite wrong because a variable means mainly that the hard-coded content is shortened to the variable-name and resolved again by the call which means there is no difference between them - results and run-times must be the same. If not - it means that the variable has another content.

Re: Bad performance using variable to calculate field

pedrohenriqueperna — Tue, 19 Dec 2023 14:32:26 GMT

Marcus, it seems to be working perfectly. Thank you very much for the tip. As for the processing it's back to normal, so the problem was indeed the variable I was using. It's really weird, because of what I said. The variable is not being used at all after the first table loaded (that loads quite fast). To make sure, I set the same variables to Null right after the table is loaded, and for some reason it jams the whole script.

I guess it must be something related to how I nested the variables and text, but it doesn't matter anymore as it's working now.

Thanks!

Re: Bad performance using variable to calculate field

pedrohenriqueperna — Tue, 19 Dec 2023 15:29:49 GMT

@marcus_sommer

Marcus, I just got a case that the space is not being considered 😞

I have this table for the mapping

ReplaceMap:

LOAD * Inline [

A, B

"AVENIDA ",

"ESTRADA ",

"DR ",

[...]

];

And have this string: AVENIDA DA PEDRA BRANCA

The output is giving me: DA PEA BRANCA (it's removing DR from withing PEDRA)

Any ideas how to make it consider the space?

Re: Bad performance using variable to calculate field

marcus_sommer — Tue, 19 Dec 2023 15:50:25 GMT

Just try it with: Verbatim | Qlik Cloud Help

Re: Bad performance using variable to calculate field

pedrohenriqueperna — Tue, 19 Dec 2023 17:06:31 GMT

Setting Verbatim to 1 solved the problem! Didn't have a clue about this system variable. Thanks for the tip

Re: Bad performance using variable to calculate field

pedrohenriqueperna — Wed, 20 Dec 2023 19:26:50 GMT

@marcus_sommer

Hi, marcus

I'm almost done dealing with special cases, but one. I'm hoping you could help me again.

Part of the script routine involves eliminating prepositions and addresses terms like "of, from, to..." and "street, avenue..." to compare 2 strings.

Setting the verbatim to 1 helped me to compare strings using spaces as delimiter, but there are some very special cases that a address string starts with a preposition and have no leading spaces, e.g:

AVENIDA DAS FLORES = Normal string, where "DAS" is a preposition that will be removed with mapsubstring searching for " DAS " and "AVENIDA" will be removed searching for "AVENIDA ".

DAS FLORES = Special string where "AVENIDA" is already not present, but in this case I have a "DAS " substring that will not be mapped with " DAS ". If I add "DAS " to the table map it will remove every substring that ends with "DAS " as well (e.g "LINDAS " will be replaced to "LIN").

So what I thought I could do is simple concatenate a space before the string, something like: ' '&'DAS FLORES'. This way mapsubstring would still read " DAS " and proceed with the replacement, but for some reason it doesn't work when adding the space inside the SET variable text:

SET vReplaceRuaODS = If(IsNum(SubField(cliente_rua_temp, ' ', SubStringCount(cliente_rua_temp, ' ') + 1))
and Not(Exists(TermosEndereco, SubField(Upper(cliente_rua_temp), ' ', SubStringCount(cliente_rua_temp, ' '))))
and SubStringCount(cliente_rua_temp, ' ') > 0,
Trim(PurgeChar(MapSubString('ReplaceMap', MapSubString('ReplaceMap', Upper(cliente_rua_temp))), '1234567890')),
If(Exists(PreposicaoEndereco, SubField(Upper(cliente_rua_temp), ' ', 1)),
Trim(MapSubString('ReplaceMap', MapSubString('ReplaceMap', ' '&Upper(cliente_rua_temp)))),
Trim(MapSubString('ReplaceMap', MapSubString('ReplaceMap', Upper(cliente_rua_temp))))));

I tried adding the string as inline with a space just for testing and it works, but it won't work when I add the space in the text for the variable. I also tried using Chr(32) and Chr(160), but with no sucess.

This must be simple. Do you have any clue how to achieve this?

Re: Bad performance using variable to calculate field

marcus_sommer — Thu, 21 Dec 2023 10:54:56 GMT

In general the approach should work. Noticeable is that there are no spaces between the concat-parts which prevents in some scenarios the intended parsing. Therefore you could try it like:

... ' ' & Upper(cliente_rua_temp) ...

or alternatively:

... Upper(' ' & cliente_rua_temp) ...

Beside this is each replace-approach difficult to (practically) impossible if there is any overlapping between the replace-substrings and/or their position and frequency is not unique definable. Some tasks are solvable by applying the replace in multiple steps which are hierarchically ordered and are only applied on certain substrings.

But in more complex scenarios I would tend not to try to clean the entire string at ones else separating each sub-string per subfield(), maybe like:

load *, len/isnum/istext(Substring) as ...;
load *, subfield(String, ' ', iterno()) as Substring, recno() as RecNo, iterno() as IterNo
from Source while iterno() <= substringcount(String, ' ');

With the len/isnum/istext() + similar checks and IterNo the content of each substring could be identified and on it afterwards specialized replace/cleaning-logic applied. The final step would be to concatenate the substrings again to the complete string with the help of RecNo and IterNo.

Re: Bad performance using variable to calculate field

pedrohenriqueperna — Thu, 21 Dec 2023 13:21:36 GMT

Hi, marcus. Thanks for the reply

It turns out to be something else that I was doing wrong. Using ' '&Upper(cliente_rua_temp) did the job after fixing it.

Still what you said is interesting, about separating the substrings. I actually do this at some part of the script, but this approach seems to be more efficient, I'll definitely try it and compare the results.

Thanks again!!