Adapting multiple records with underscore

[#1]

Dear all,

I have two tables which I would like to link to one another, but one of them contains underscores and the other one does not.

Name	Age
John Doe	40
Peter Parker	41
Jane Doe	42

Name	Weight
John_Doe	50
Peter_Parker	60
Jane_Doe	70

I prefer not to change them one by one in the code in case new records are added.

What is the syntax for removing the underscores in the Qlik Sense data load editor?

Thanks for your help.

[datanibbler]

Hi,

does QlikSense have the REPLACE() function?

If so, just replace BLANKs in that field with an underscore -> create a new field.

Then

- drop the old field using DROP FIELD ... FROM ...

- rename the new field to the old fieldname using RENAME FIELD ... TO ...

HTH

[#3]

Thanks for your reply, but unfortunately the Replace function is not supported by Qlik Sense.

[datanibbler]

Hi,

oops. That was dropped? Stupid ... well, then you can do it otherwise.

Do you have the Subfield() function?  Then you could try like

>> Subfield([name], ' ', 1) & '_' & Subfield([name], ' ', 2 <<

If that has been dropped, too - do you have the INDEX() function (to tell you the position of one character within a string?

[#5]

Hi,

thanks, the Subfield() function works and I understand what it does. Do you know how I should iterate through the data in order to apply this to every record in the name column?

[petter]

Replace function is still supported... both in scripts and charts/UI

[#7]

Are you sure? This is what I got when I entered Replace and clicked the question mark:

http://help.qlik.com/sense/en-US/online/#../Subsystems/Hub/Content/Scripting/QlikViewFunctionsStatem...

Replace

This command is only relevant during partial reload, which is not supported in Qlik Sense.

I work with Qlik Sense 1.1 which I believe is the newest version. If you are sure it works I will try it out.

[petter]

There are two "Replace" keywords in script syntax:

- Replace() function

- Replace prefix to a Load statement

We are referring to the first - which is a function to replace parts of a string with some other string.

You are talking about REPLACE LOAD .... which is something entirely different ...

[petter · Accepted solution]

So here is a way to do it in Qlik Sense:

[#10]

Thanks for your elaboration!