Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
aarondavis100
New Contributor II

change value in a table

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

Tags (2)
1 Solution

Accepted Solutions

Re: change value in a table

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

7 Replies

Re: change value in a table

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

One more stilton might be this

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
trdandamudi
Honored Contributor II

Re: change value in a table

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
New Contributor III

Re: change value in a table

Hi,

You can use applymap

aarondavis100
New Contributor II

Re: change value in a table

Thank you for your solution - worked a dream

MVP & Luminary
MVP & Luminary

Re: change value in a table

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

you're welcome

glad I could help

regards

Marco