topic Re: Changing text via the script in QlikView

Changing text via the script

beneldridge — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Can someone tell me how to change the text of something in the script?

I am wanting to make several slight changes to a column for example I would like to make such changes like "Assets London" to "Ass London" and "Assets Manchester" to "Ass Manchester" without having to create another column?

Thanks,

Ben

Re: Changing text via the script

rubenmarin — Thu, 25 Oct 2018 10:59:56 GMT

Hi Ben, you can use a mapping table, this is a table with 2 columns: one iis the value to search and the 2nd the value returned.

mapText:

LOAD * inline [

TextFrom, TextTo

Assets London, Ass London

Assets Manchester, Ass Manchester

];

And use applymap when loading the text field:

LOAD

Applymap('mapText', TextField) as TextField

...

If some value is not found in the mapText table the default value is kept.

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/MappingFunctions/ApplyMap.htm

Re: Changing text via the script

sunny_talwar — Thu, 25 Oct 2018 11:04:49 GMT

May be use a Mapping load to get this resolved

LivingQlik Roots: The Complete Guide to QlikView Mapping Load

Re: Changing text via the script

beneldridge — Thu, 25 Oct 2018 11:31:57 GMT

Hi Ruben,

Thanks for getting back to me, I tried adding the below to my coding but it did not like it. I am pretty much new to Qlikview so it is pretty alien to me, but enjoying the challenge. Are you able to help me some more?

Thanks,

Ben

LOAD * inline [

TextFrom, TextTo

Assets London, Ass London

Assets Manchester, Ass Manchester

];

Re: Changing text via the script

sunny_talwar — Thu, 25 Oct 2018 11:34:46 GMT

I think you missed the Mapping keyword

MappingTable:

Mapping

LOAD * inline [

TextFrom, TextTo

Assets London, Ass London

Assets Manchester, Ass Manchester

];

Re: Changing text via the script

awhitfield — Thu, 25 Oct 2018 11:38:09 GMT

Ha ha, that's a really bad abbrv. for Assets!

Andy

Re: Changing text via the script

beneldridge — Thu, 25 Oct 2018 11:47:41 GMT

Thanks Sunny,

I am still getting a script error saying: Field Not Found - <Business Unit> followed by the script I tried to add?

Thanks,

Ben

Re: Changing text via the script

Colin-Albert — Thu, 25 Oct 2018 12:13:48 GMT

Another option would be to use replace() to change 'Assets' to 'Ass'

Something along the lines of

Load

replace(your_field_name, 'Assets', 'Ass') as your_field_name,

....

from .... ;

You can also nest multiple replace statements together

replace(replace(your_field_name, 'Assets', 'Ass'), 'AAA', XXX') as your_field_name,

If there are lots of changes, then the mapping table method is easier to maintain.