Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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
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.
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))
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
]
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?
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.
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
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.
Thank you very much this is very helpful!
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??