topic Re: ApplyMap result using an if statement in QlikView

ApplyMap result using an if statement

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

So I am using an ApplyMap and the results are correct. ... sort of.

My resultant table should only show X,Y,Z and not have A and B also.

My group has more names than listed below but only those 3 names below are using the ApplyMap functionality. The other names should then be ignored in my resultant table.

In my resultant table, for my dimension, I am using

if(Service = 'A*', NULL(), Service)

This is the important part, I need the wildcard A*, rather than say

if(Service = 'A, AB, ABC, ABCD', NULL(), Service)

Resultant table:

Service	Number
X	1
Y	2
Z	3
A	4
AB	5
ABC	7
ABCD	8

Mapping table

Group	Service
John	X
Mary	Y
Rob	Z

Re: ApplyMap result using an if statement

Colin-Albert — Thu, 14 Apr 2016 13:42:27 GMT

Your question does not show the applymap statement you are using. Can you post your applymap statement?

Re: ApplyMap result using an if statement

sunny_talwar — Thu, 14 Apr 2016 13:42:49 GMT

What is the source data look like. I think I am not able to understand the complete issue here.

Re: ApplyMap result using an if statement

avinashelite — Thu, 14 Apr 2016 13:56:56 GMT

Try like this

Temp:

Mapping LOAD

Service,

Group

from table

where Service='X' or Service='Y' or Service='Z' ;

Resultant_Table:

LOAD

Service,

Number,

applymap('Temp',Service,null()) as Group

from table;

//since the mapping table has only the values for X ,Y and Z rest for the values will return as null()

Hope this helps you

Re: ApplyMap result using an if statement

bobbydave — Thu, 14 Apr 2016 13:58:04 GMT

ApplyMap('MapServiceToGroup', Group) as Service

I am getting the correct results but at the same time, I only want the X Y and Z to show.

Attached is a dummy app, The first table shows my result, the second shows my Wildcard if statement

Re: ApplyMap result using an if statement

sunny_talwar — Thu, 14 Apr 2016 13:59:47 GMT

Have you attached the correct qvw? I am only seeing one table

Re: ApplyMap result using an if statement

avinashelite — Thu, 14 Apr 2016 14:00:47 GMT

did you try my suggestion (please check my last post )

Re: ApplyMap result using an if statement

bobbydave — Thu, 14 Apr 2016 14:02:08 GMT

Attached again

Re: ApplyMap result using an if statement

avinashelite — Thu, 14 Apr 2016 14:04:26 GMT

Try like this

Temp:

Mapping LOAD

Service,

Group

from table

where Service='X' or Service='Y' or Service='Z' ;

Resultant_Table:

LOAD

Service,

Number,

applymap('Temp',Service,null()) as Group

from table;

//since the mapping table has only the values for X ,Y and Z rest for the values will return as null()

Re: ApplyMap result using an if statement

bobbydave — Thu, 14 Apr 2016 14:05:19 GMT

This is exactly what I needed. I didn't have the NULL() at the end of my ApplyMap() function.

Thanks