Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

combining values in a field

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.

 

CountryYNtotal number
US12345..
UK34234..
China22234..
....

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

and my second questions is how to make such table.

Thanks!!!

1 Solution

Accepted Solutions
MVP
MVP

Re: combining values in a field

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.

View solution in original post

7 Replies
Not applicable

Re: combining values in a field

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

MVP
MVP

Re: combining values in a field

one solution could be a new field


1.png


load

  yesno,

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

inline [

yesno

y

Y

Yes

YES

n

N

no

No

NO

]

Not applicable

Re: combining values in a field

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?

saran7de
Contributor III

Re: combining values in a field

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.

MVP
MVP

Re: combining values in a field

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.

View solution in original post

Not applicable

Re: combining values in a field

Thank you very much this is very helpful!

Not applicable

Re: combining values in a field

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