topic Re: combining values in a field in QlikView

combining values in a field

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a field that contains either Yes or No.

But the exact value of the fields are N, n, No, NO and Y, y, Yes, YES.

I want to combine all the NOs and YESes and combine them into two columns.

what I eventually want to do is to make a simple two dimensional table.

Country	Y	N	total number
US	12	345	..
UK	34	234	..
China	22	234	..
…	..		..

so my first question is how to combine certain values in one.

and my second questions is how to make such table.

Thanks!!!

Re: combining values in a field

Tue, 29 Mar 2016 19:19:54 GMT

You can achieve this from script when load the info or use a calculated dimension in a pivot table, in both case you should use a expression like this to get the value:

upper(Left(field,1))

Re: combining values in a field

maxgro — Tue, 29 Mar 2016 19:38:09 GMT

one solution could be a new field

load

yesno,

Pick(WildMatch(yesno, '*n*', '*'), 'N', 'Y') as newyesno

inline [

yesno

Yes

YES

]

Re: combining values in a field

Tue, 29 Mar 2016 20:05:21 GMT

I am pulling directly from my database.

and i think I might be able to take advantage of your answer.

could you explain little more?

Pick(WildMatch(yesno, '*n*', '*'), 'N', 'Y') as newyesno

- what does wildmatch do?

- how does the remaining syntax work?

inline

- please explain what inline does.

and after all this, will there be an extra column like the screenshot above?

Re: combining values in a field

Saravanan_Desingh — Tue, 29 Mar 2016 20:45:36 GMT

Hi,

You have mentioned that the Field yesno has different forms ( N, n, No, NO and Y, y, Yes, YES). So, Maxgro formatted it to have only two values N or Y and named it as newyesno field.

Now you can use Country and newyesno fields in your Chart object.

Here Load-Inline is used to give different forms of Yes and No.

WildMatch used for case insensitive match here.

Re: combining values in a field

maxgro — Tue, 29 Mar 2016 21:06:52 GMT

I am pulling directly from my database.

and i think I might be able to take advantage of your answer.

could you explain little more?

Pick(WildMatch(yesno, '*n*', '*'), 'N', 'Y') as newyesno

- what does wildmatch do?

- how does the remaining syntax work?

the script from a database could be like

load

field1,

field2,

Pick(WildMatch(fieldwithyesno, '*n*', '*'), 'N', 'Y') as newyesno;

sql select

field1,

field2,

fieldwithyesno

from tableinyourdatabase;

this is a preceding load

load

...;

sql select

...;

read here for detail

Preceding Load

regarding Wildmatch, this is from Qlik help

wildmatch( str, expr1 [ , expr2,...exprN ] )

The wildmatch function performs a case insensitive comparison and permits the use of wildcard characters ( * and ?) in the comparison strings.

Example:

wildmatch( M, 'ja*','fe?','mar')

returns 1 if M = January

returns 2 if M = fex

regardin pick, always from Qlik help

pick(n, expr1[ , expr2,...exprN])

Returns the n:th expression in the list. n is an integer between 1 and N.

In my script

WildMatch(fieldwithyesno, '*n*', '*') returns 1 for n, no, No, not, .... and 2 for all other (y yes, Yes, ....)

Pick(Wildmatch......) returns N when wildmatch returns 1 (n, no, ....), Y when wildmatch returns 2 (y yes, ....)

this line

Pick(WildMatch(fieldwithyesno, '*n*', '*'), 'N', 'Y') as newyesno

add a field, the field name is newyesno and the values N or Y (this new field is the extracolumn)

inline

- please explain what inline does.

and after all this, will there be an extra column like the screenshot above?

I haven't your data. I used inline just to make some test data (yes, no, y, .....) for your question.

Well, inline too (and the inline wizard) is in the Qlik online help.

Re: combining values in a field

Tue, 29 Mar 2016 21:10:57 GMT

Thank you very much this is very helpful!

Re: combining values in a field

Tue, 29 Mar 2016 21:38:54 GMT

Hi,

But I still get bunch of dashes. and I know that all those should be N. I don't understand why these are still remaining as -.

these might be from NULLs. is there any way i can make NULLs into N??