topic Re: change value in a table in QlikView

change value in a table

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

So hopefully this is my last quirk.

I am loading a table into qlikview, via a qvd.

Now the field itself (SLM_STATUS) has values in it stored as 1, 2 and 3. So a particular ticket can be a 1, 2 or a 3.

What i need to do is where the value is 1, i want it to show as "Met SLA"

2 to show as "Escalated"

3 to show as "Breached SLA"

The extract from the script is as follows

incident_data:

LOAD

FLD_TICKETID,

Status,

SLM_STATUS

FROM

\\qvtest\QlikView_Files\qvds\final\group\remedy\OP_Request_final.qvd

(qvd)

where not WildMatch(Status,'Withdrawn');

Ultimately, i want a pie chart showing the split, and also need to calculate a compliance of those that have breached against the total number of tickets.

Thanks in advance

Re: change value in a table

MarcoWedel — Tue, 04 Oct 2016 21:24:04 GMT

Hi,

one solution could be to use ApplyMap, e.g. like this:

mapSLMStatus:
Mapping LOAD * Inline [
SLM_STATUS, SLM_STATUS_ALIAS
1,Met SLA
2,Escalated
3,Breached SLA
];

incident_data:
LOAD FLD_TICKETID,
          Status,
          ApplyMap('mapSLMStatus',SLM_STATUS) as SLM_STATUS
FROM \\qvtest\QlikView_Files\qvds\final\group\remedy\OP_Request_final.qvd (qvd)
where not WildMatch(Status,'Withdrawn');

hope this helps

regards

Marco

Re: change value in a table

Anil_Babu_Samineni — Wed, 05 Oct 2016 01:55:14 GMT

One more stilton might be this

Pick(field, '1','2','3', match('expected1','expected 2','expected 3'))

Re: change value in a table

trdandamudi — Wed, 05 Oct 2016 03:28:18 GMT

Usually, if you have long list of values to be converted then use Apply map as Marco mentioned above. Otherwise you can use the below expression to get what you want:

=Pick(match(SLM_STATUS,1,2,3),'Met SLA','Escalated','Breached SLA')

Hope this helps...

Re: change value in a table

rajpreeths — Wed, 05 Oct 2016 07:34:45 GMT

Hi,

You can use applymap

Re: change value in a table

Anonymous — Wed, 05 Oct 2016 09:11:51 GMT

Thank you for your solution - worked a dream

Re: change value in a table

rwunderlich — Wed, 05 Oct 2016 13:06:57 GMT

I would suggest a slight mod to Marco's excellent solution:

Dual(ApplyMap('mapSLMStatus',SLM_STATUS),SLM_STATUS) as SLM_STATUS

The addition of the dual() provides the ability to set the sort order in a chart to numeric and have the status values sort corectly.

-Rob

Re: change value in a table

MarcoWedel — Wed, 05 Oct 2016 21:37:42 GMT

you're welcome

glad I could help

regards

Marco