change value in a table

[Anonymous]

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

[MarcoWedel · Accepted solution]

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

[Anil_Babu_Samineni]

One more stilton might be this

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

[trdandamudi]

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...

[rajpreeths]

Hi,

You can use applymap

[Anonymous]

Thank you for your solution - worked a dream

[rwunderlich]

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

[MarcoWedel]

you're welcome

glad I could help

regards

Marco