combining values in a field

[#1]

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!!!

[#2]

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))

[maxgro]

one solution could be a new field

load

  yesno,

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

inline [

yesno

y

Y

Yes

YES

n

N

no

No

NO

]

[#4]

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?

[Saravanan_Desingh]

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.

[maxgro · Accepted solution]

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.

[#7]

Thank you very much this is very helpful!

[#8]

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??