Data matching

[#1]

Chaps,

Hopefully you can help me. I have a set of data that has four columns. At present a value is produced in our system for 'Rostered Pay' every day unless the code includes RRR. This setting is a defined setting in the allocation software.

I now want to do as similar thing through Qlikview, so basically where the 'Rostered Duty' includes characters * E L M or B as part of the code then I want the 'Rostered Pay' to be zero, obviously I can do this through if statements, but as we have 25 sites the if statement would be too long and cumbersome. Is there a shorter way of doing this, perhaps using the MATCH function?

Date	Act Pay	Rostered Pay	Rostered Duty
30/01/2010 00:00:00	112.21	140.18	KX120
31/01/2010 00:00:00	112.21	125.21	KX118
01/02/2010 00:00:00	0	0	KXRRR
02/02/2010 00:00:00	0	0	KXRRR
03/02/2010 00:00:00	132.87	112.21	KX116
04/02/2010 00:00:00	0	112.21	KX116
05/02/2010 00:00:00	79.15	112.21	KX116
06/02/2010 00:00:00	125	112.21	KX*
07/02/2010 00:00:00	118.45	112.21	KXE
08/02/2010 00:00:00	140.23	112.21	KXL
09/02/2010 00:00:00	113.83	112.21	KXM

[#2]

You can use a combination of wildmatch() and pick() functions:

=pick(wildmatch('klb','*E*','*L*','*B*','*M*','*'),'priceE', 'priceL','priceB','priceM', 'PriceStar')

But be careful about the elements' order in the lists of codes and prices. In the example the result is priceL because L in the condition string is earlier than B.

[#3]

Thanks Nick, I'm not sure your solution will work for me .I think perhaps i didnt explain myself properly.

I effectively want to do a lookup on the column "Rostered Duty', I want the look to check the value in the Rostered Duty column and if the value contains on of the following characters

*

e

l

m

after the first two digits I want the "Rostered Pay Value " to be populated as 0.00 else just put in the current "Rostered Pay Value"

[#4]

So, you can do this:

if(wildmatch('klb','*E*','*L*','*B*','*M*','*') > 0, 0, "Rostered Pay Value"))

[#5]

Hi Nick,

Thanks for your reply once again. I have tried what you suggested, although I wasn't sure what the 'klb' was related to ?

I have attached an example app, but unforutnately the column wildmatch which has your suggested formula in it just gives me Zeros.

[#6]

It shouldn't work in your application because it's example. You need adopt it according to your logic and the function description in the 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.
 <h2>Example:</h2>
wildmatch( M, 'ja*','fe?','mar')


returns 1 if M = January
returns 2 if M = fex

The "*" symbol is reserved. You need to replace this by something else. Check the attachment for more details.

[#7]

If I want January, fex instead of 1, 2 etc..

what would be the solution.

Thanks,

[#8]

pick(wildmatch( M, 'ja*','fe?','mar'), 'January', 'fex', 'mar');

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

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

Example:

pick( N, 'A', 'B', 4 )

returns 'B' if N = 2

returns 4 if N = 3