How do I replace/change values under a column while loading data?

[#1]

Hi everybody!

Really pumped to have found Qlik Sense this past week. I've already been able to create some amazing visualizations for our Call Center.

I have a question surrounding the ability to change the values found under a column during the data load. For instance, I have a column of customer satisfaction scores that range from 1-10. The values in that column are 1-10, but we find the CSAT score by ascribing these values to those responses:

0-6 = -100

7-8 = 0

9-10 = 100

...Then we find the average score from that 'grading scale'.

I'm wondering if it's possible for Qlik Sense to automatically change all of my values to what I have listed above?

Currently, this is a part of what I have appearing in the data load editor:

LOAD

    "MONTH",

    SRTL,

    TL as "TL Name",

    TENURE,

    completion_date as "DATE",

    CTN,

    Rep_ATTUID as "REP ATTUID",

    Rep_Name as REP,

    Level3 as L3,

    Resolution,

    level1 as L1,

    WTR_Score as WTR,

FROM [lib://Desktop/Qlik\voiceraw.xlsx]

(ooxml, embedded labels, table is Sheet1);

Is this where I would take the results for 'WTR' and code in the value changes?

Thanks in advance for any help you can provide!

[#2]

So far, I got started with this:

WTR_Score, replace(replace(WTR_Score,'10','100'),'1','-100') as WTR,

But it's returning the 100 into a -1000 when it goes through the second replacement.

[#3 · Accepted solution]

In case this helps somebody in the future, the solution I was able to find was:

WTR_Score, if(WTR_Score > '8','100',if(WTR_Score < '7','-100',if(WTR_Score > 6 and WTR_Score < 9,'0','-'))) as WTR,

[Michael_Tarallo]

Hello Dustin,

Welcome aboard! Nice to see you are making great progress. I hope you find your stay here with us enjoyable. HA! Seriously man, nice work investigating and finding what you needed. Thanks for your question, we are here to help and will do our best to make you comfortable with the product.

If you have not see this - please take a look at: New to Qlik Sense Videos - to learn more - an examples similiar to what you are looking for could be available. For example Dimension grouping - Qlik Sense - Dimension Grouping (video) - provides a similar example to what you performed here.

Keep in touch with us and let us know how you do:

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik Community Advocate

Senior Technical Product Marketing Manager

[rvalenzuela]

Hi, I have a question on this subject.

What if on that same field you had, for example, values from 0 to 10 and also from 100 to 199 and the ones over 100 you don't want to change them.

What should I use to keep the same value read on the field?

Thanks

[jbchurchill]

Bump to Rvalenzuela's Question. I only want to change one numeric value so I'm not sure I can do it without somehow accounting for every number?