topic Re: Mapping a respone to Null in QlikView

Mapping a respone to Null

Wed, 25 Nov 2020 16:16:04 GMT

Good morning.

I am new to Qlikview and need help with the following please.

I have data that reads

1-Female

2-Male

3-Not Answered

I have mapped the answers in a mapping spread sheet to display as

Female

Male

Not Answered

I have produced a graph that shows the percentage split for all of the answers which gives the following results

Female 53.51%

Male 42.73%

Not Answered 3.75%

How do I ignore 'Not Answered' in my calculation please? Will setting the mapping spread sheet to the following work?

Header 1	Header 2

1-Female	Female
2-Male	Male
3-Not Answered

Many thanks

Phil

Re: Mapping a respone to Null

oknotsen — Tue, 23 May 2017 07:16:01 GMT

I think excluding that value by using Set Analysis will do the trick.

Problem is that when you are really new to Qlik, Set Analysis might be a bit frustrating to figure out on your own. So I suggest you share with us the field names involved in this chart (so both the dimension and the expression) and we can try to give you an example of the Set Analysis statement based on those fields and expression.

Re: Mapping a respone to Null

Tue, 23 May 2017 07:22:01 GMT

Thanks for the speedy reply. Is this what you require?

Re: Mapping a respone to Null

oknotsen — Tue, 23 May 2017 07:39:00 GMT

Might be .

Okay, I suggest you change your expression from...

count(Gender_SQ)

... to this...:

count( { < Gender_SQ =- {'Not Answered'} > } Gender_SQ)

I am not going to guarantee it works, but I hope it is a serious step in the right direction.

Please notice the use of single quotes and minus right after the equal sign.

Re: Mapping a respone to Null

Tue, 23 May 2017 07:45:39 GMT

I am receiving the following error message when using

=Count{ < Gender_SQ =- {'Not Answered'} > } Gender_SQ)

Re: Mapping a respone to Null

quilldew — Tue, 23 May 2017 07:50:36 GMT

Hi Phil,

Assuming the field name containing the gender is Gender_SQ you could try

Count({$<[Gender_SQ]-={'Not Answered'}>}[Gender_SQ])

or if you still have the number prefixes

Count({$<[Gender_SQ]-={'3-Not Answered'}>}[Gender_SQ])

If the field name is 'Header 1' try

Count({$<[Header 1]-={'Not Answered'}>}[Gender_SQ])

Also instead of using a mapping table you could strip the first two characters on import of your table as an alternative. Not sure which would be faster.

Re: Mapping a respone to Null

quilldew — Tue, 23 May 2017 07:52:42 GMT

You have removed the first '(' . Place a '(' in front of the '{'.

Re: Mapping a respone to Null

oknotsen — Tue, 23 May 2017 08:03:03 GMT

There should be a ( before that { .

I noticed I forgot to do that myself after I posted. I edited my post, but I guess you jumped it before I was able to correct my mistake and you copied my mistake .

Re: Mapping a respone to Null

Tue, 23 May 2017 08:09:15 GMT

=Count({ < Gender_SQ =- {'Not Answered'} > } Gender_SQ)

This has worked perfectly.

Thanks you very much for your help. Very much appreciated.