topic Re: Changing names in QlikView

Changing names

marcvw — Mon, 22 Jan 2024 13:29:24 GMT

Good Day All,

Another day, another challenge, I have a column that contains the names of differrent organizations. I woul like to make changes to these names via an expression in Qlikview. Are there any ways in order to do so?

See below example.

ID	Current name	Future name
1	abc b.v.	abc bv
2	def gmbh	def GMBH
3	ghi bvba	ghi b.v.b.a

Appreciate your support in here.

Re: Changing names

anat — Mon, 22 Jan 2024 16:49:23 GMT

seems your requirement is not static like for each record you have to write different condition like below

LOAD *,Pick(Match(id,1,2,3),Replace(name,'.',''),SubField(name,' ',1)&' '&Upper(SubField(name,' ',2)),SubField(name,' ',1)&' '&SubField(name,' ',2)) as Nname;
LOAD * Inline [
id,name
1,abc b.v.
2,def gmbh
3,ghi bvba

];

may be confirm your requirement again.....

Re: Changing names

rwunderlich — Mon, 22 Jan 2024 17:22:55 GMT

This is an ideal use for a Mapping Table and Map Using or ApplyMap.

https://help.qlik.com/en-US/sense/November2023/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/Mapping.htm

NameMap:
Mapping Load * Inline [
From, To
abc b.v., abc bv
def gmbh, def GMBH
ghi bvba, ghi b.v.b.a
];

Map CustomerName Using NameMap;

Customers:
LOAD CustomerName, ...

The mapping table needs only to include names that will change. Names not in the table will remain unchanged.

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Re: Changing names

marcvw — Tue, 23 Jan 2024 08:10:33 GMT

Thank you for your response in here Rob. The challenge that I'm faced with is that I'm not able to utilize the load functionality as we are deploying the Qlikview dashboard in a different application. Therefore restricted with only expressions.

Apologize for not being clear on this however if you may have a solution in order to perform this with an expression, feel free to share this with me.

I've tried the following without succes:

=IF(
Upper(Company_Name) = 'abc b.v.', 'abc bv',
Upper(Company_Name) = 'def gmbh', 'def GMBH',
Upper(Company_Name) = 'ghi bvba', 'ghi b.v.b.a',
Company_Name))

Unfortunately when this is performed for 1 line, it is functioning however for 2 or more lines, I'm getting an error message.

Again, thank you for your response in here.

Regards,

Marc

Re: Changing names

marcvw — Tue, 23 Jan 2024 08:11:25 GMT

Thank you for your response in here . The challenge that I'm faced with is that I'm not able to utilize the load functionality as we are deploying the Qlikview dashboard in a different application. Therefore restricted with only expressions.

Apologize for not being clear on this however if you may have a solution in order to perform this with an expression, feel free to share this with me.

I've tried the following without succes:

=IF(
Upper(Company_Name) = 'abc b.v.', 'abc bv',
Upper(Company_Name) = 'def gmbh', 'def GMBH',
Upper(Company_Name) = 'ghi bvba', 'ghi b.v.b.a',
Company_Name))

Unfortunately when this is performed for 1 line, it is functioning however for 2 or more lines, I'm getting an error message.

Again, thank you for your response in here.

Regards,

Marc

Re: Changing names

anat — Tue, 23 Jan 2024 08:30:53 GMT

Pick(Match(Company_Name,'abc b.v.','def gmbh','ghi bvba'),'abc bv','def GMBH','ghi b.v.b.a') as Nname

Re: Changing names

marcvw — Tue, 23 Jan 2024 08:56:21 GMT

Thanks again and I'm sure this expression functions however in the environment we are working we are not able to create a new column. We can only work with expressions and create expressions however not new dimensions. Again apologize this was not clear. Hope that there are other solutions you might be aware of and are willing to share.

Thanks in advance.

Re: Changing names

rwunderlich — Tue, 23 Jan 2024 16:28:33 GMT

@anat suggestion of Pick(Match()) is good, just don't use the "as Nname" if you are using it in a chart.

If you want to use the If() form, read up in the help on the if() syntax. You will need an If() function for each condition. https://help.qlik.com/en-US/sense/November2023/Subsystems/Hub/Content/Sense_Hub/Scripting/ConditionalFunctions/if.htm

=IF(Lower(Company_Name) = 'abc b.v.', 'abc bv'
,if(Lower(Company_Name) = 'def gmbh', 'def GMBH'
,if(Lower(Company_Name) = 'ghi bvba', 'ghi b.v.b.a'
,Company_Name)))

-Rob