Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
One more stilton might be this
Pick(field, '1','2','3', match('expected1','expected 2','expected 3'))
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...
Hi,
You can use applymap
Thank you for your solution - worked a dream
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
you're welcome
glad I could help
regards
Marco