Bad performance using variable to calculate field

[therealdees]

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?

[therealdees]

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

[marcus_sommer]

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().

[therealdees]

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

[marcus_sommer · Accepted solution]

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.

[therealdees]

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!

[therealdees]

@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?

[marcus_sommer · Accepted solution]

Just try it with: Verbatim | Qlik Cloud Help

[therealdees]

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

[therealdees]

@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?