Is it possible to modify dimensions Text results within a graph?

[#1]

I have data that is not very clean that I am trying to report on, and i am trying to figure out how to modify how some of the data is displayed within a graph.

I am trying to create a simple pie chart that shows that % of incoming calls that are Spanish vs English.

I do have a dimension that organizes this data but not in a clean manor. The dimension options are "Office", "Office Spanish", or "[None]".

I would like to change the options from the given text to "English", "Spanish", "Unknown".

Is this possible? I am pretty new to creating load Scripts within Qlik Sense so I have not been able to come up with a possible solution myself as I am still trying to understand what can and cant be done, but i figured something like this should be possible...

Any help will be greatly appreciated.

Thanks!

[Gysbert_Wassenaar]

You can replace the values in the script:

mapReplace:

MAPPING LOAD * INLINE [

Old, New

Office, English

Office Spanish, Spanish

[None], Unknown

];

Data:

LOAD

     ...some fields...,

     ApplyMap('mapReplace', MyField) as MyField

FROM

     ...;

There are more ways to do this, but with a mapping table you can easily add as many mappings as you need. You can also create such a list in an excel file to easily maintain that list and then load the mapping table from the excel file.

[reddy-s]

Hi Paul,

This has to be done in the script using a mapping load as Gysbert mentioned. You will not be able to do it in the graph itself.

Thanks,

Sangram.

[Gysbert_Wassenaar]

That's not correct. But a calculated dimension is not the best way to deal with this.

[#5]

Hey Gysbert, Thanks for the advice!

I tried creating a script based on your example but something is off as I keep getting errors when a debug/load the script.

This is what I created:

LanguageMap:
MAPPING LOAD * INLINE[
Skill, Language
  Office,English
  Office Spanish,Spanish
[None],Unknown
];

Call_Data:
LOAD
    [Calls],
[Date]   
ApplyMap('LanguageMap',[Skill],Null()) as [Skill]
Resident [Data];

Currently the error I am getting when debugging is:

Syntax error, missing/misplaced FROM: LanguageMap: MAPPING LOAD * INLINE[ Skill, Language Office,English Office Spanish,Spanish [None],Unknown ]: LanguageMap: MAPPING LOAD * INLINE[ Skill, Language Office,English Office Spanish,Spanish [None],Unknown ]

Have any idea what I have scripted incorrectly, I am thinking it has to do with the brackets that are around the " [None] "option within the LanguageMap section. But that is how the text field is populated within the data, so I don't know how to get around the brackets.

[mgranillo]

Yes, I'd recommend avoiding calculated dimensions when possible.  When I first started developing in Qlik I used calculated dimensions throughout my apps and suffered horrible processing speed problems.  So get in the habit of putting them in the load script.

[#7]

Hi Paul,

You can add this expression in the 'Edit Expression' of Dimension option

if(DimensionField='Office','English',

if(DimensionField='Office Spanish','Spanish',

if(DimensionField='[None]','Unknown')))

You can use the same script in the 'Load Script' of QlikSense. Here, in the load script, an extra column will be generated having the new dimension values i.e. 'English', 'Spanish' and 'Unknown'.

As Gysbert mentioned, this is not the best way. But it is one way of doing it.

Regards,

Rohan