Qlik sense : use the pick and match function together

Question

how to understand pick(match("Care Type",'1','6','7','11'),'1','4','5','M','') as care_type,

how to use the pick and match together

Answer

Match() returns a number depending on the [Care Type] value.

If [Care Type] is 1 then Match() returns 1

If [Care Type] is 6 then Match() returns 2

If [Care Type] is 7 then Match() returns 3

If [Care Type] is 11 then Match() returns 4

If [Care Type] is any other number then Match() returns 0

In a pick(match()) you use the value from your Match() to pick the Nth value in your pick list.

If? Match() returns 1 the pick() will return 1

If? Match() returns 2 the pick() will return 4

If? Match() returns 3 the pick() will return 5

If? Match() returns 4 the pick() will return M

If? Match() returns 0 the pick() will return nothing

So it will be?pick(match("the field to match",'match value 1','match value 2','match value 3','match value 4'),'match result 1','match result 2','match result 3','match result 4','default value if not matches')? ?—— by?zhengkai.blog.csdn.net

pick - script and chart function

The pick function returns the?n:th expression in the list.

Syntax:??

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

Arguments:??

Arguments

Argument	Description
n	n?is an integer between 1 and?N.

Example:??

Example

Example	Result
pick( N, 'A','B',4, 6 )	returns?'B'?if?N?=?2 returns?4?if?N?=?3

match - script and chart function

The?match?function compares the first parameter with all the following ones and returns the numeric location of the expressions that match. The comparison is case sensitive.

Syntax:??

match(?str, expr1 [ , expr2,...exprN ])

Tip noteIf you want to use case insensitive comparison, use the?mixmatch?function. If you want to use case insensitive comparison and wildcards, use the?wildmatch?function.

Some of the examples in this topic use inline loads. For more information, see?Inline loads.

Example: Load script using?match

Example: Load script

Load script

You can use?match?to load a subset of data. For example, you can return a numeric value for an expression in the function. You can then limit the data loaded based on the numeric value.?Match?returns 0 if there is no match. All expressions that are not matched in this example will therefore return 0 and will be excluded from the data load by the WHERE statement.

Create a new tab in the data load editor, and then load the following data as an inline load. Create the table below in?Qlik Sense?to see the results.

Transactions:
Load * Inline [
transaction_id, transaction_date, transaction_amount, transaction_quantity, customer_id, size, color_code
3750, 20180830, 23.56, 2, 2038593, L, Red
3751, 20180907, 556.31, 6, 203521, m, orange
3752, 20180916, 5.75, 1, 5646471, S, blue
3753, 20180922, 125.00, 7, 3036491, l, Black
3754, 20180922, 484.21, 13, 049681, xs, Red
3756, 20180922, 59.18, 2, 2038593, M, Blue
3757, 20180923, 177.42, 21, 203521, XL, Black
];
 
/*
Create new table called Transaction_Buckets
Create new fields called Customer, and Color code - Blue and Black
Load Transactions table.
Match returns 1 for 'Blue', 2 for 'Black'. 
Does not return a value for 'blue' because match is case sensitive.
Only values that returned numeric value greater than 0 
are loaded by WHERE statment into Transactions_Buckets table.
*/
 
Transaction_Buckets:
Load
 customer_id,
 customer_id as [Customer], 
 color_code as [Color Code Blue and Black]
Resident Transactions
Where match(color_code,'Blue','Black') > 0; Copy code to clipboard

Results

Qlik Sense?table showing the output from using the?match?function in the load script

Color Code Blue and Black	Customer
Black	203521
Black	3036491
Blue	2038593

Examples - Chart expressions using?match

Examples: Chart expressions

Chart expression 1

Load script

Create a new tab in the data load editor, and then load the following data as an inline load. After loading the data, create the chart expression examples below in a?Qlik Sense?table.

MyTable:
Load * inline [Cities, Count
Toronto, 123
Toronto, 234
Toronto, 231
Boston, 32
Boston, 23
Boston, 1341
Beijing, 234
Beijing, 45
Beijing, 235
Stockholm, 938
Stockholm, 39
Stockholm, 189
zurich, 2342
zurich, 9033
zurich, 0039];Copy code to clipboard

The first expression in the table below returns 0 for Stockholm because 'Stockholm' is not included in the list of expressions in the?match?function. It also returns 0 for 'Zurich' because the?match?comparison is case-sensitive.

Qlik Sense?table showing examples of the?match?function in a chart expression

Cities	match( Cities,'Toronto','Boston','Beijing','Zurich')	match( Cities,'Toronto','Boston','Beijing','Stockholm','zurich')
Beijing	3	3
Boston	2	2
Stockholm	0	4
Toronto	1	1
zurich	0	5

Chart expression 2

You can use match to perform a custom sort for an expression.

By default, columns sort numerically or alphabetically, depending on the data.

Qlik Sense?table showing an example of the default sort order

Cities
Beijing
Boston
Stockholm
Toronto
zurich

To change the order, do the following:

1. Open the?Sorting?section for your chart in the?Properties?panel.
2. Turn off auto sorting for the column on which you want to do a custom sort.
3. Deselect?Sort numerically?and?Sort alphabetically.

4. Select?Sort by expression, and then enter an expression similar to the following:

   =match( Cities, 'Toronto','Boston','Beijing','Stockholm','zurich')

   The sort order on the?Cities?column changes.

Qlik Sense?table showing an example of changing the sort order using the?match?function

Cities
Toronto
Boston
Beijing
Stockholm
zurich

You can also view the numeric value that is returned.

Qlik Sense?table showing an example of the numeric values that are returned from the?match?function

Cities	Cities & ' - ' & match ( Cities, 'Toronto','Boston', 'Beijing','Stockholm','zurich')
Toronto	Toronto - 1
Boston	Boston - 2
Beijing	Beijing - 3
Stockholm	Stockholm - 4
zurich	zurich - 5