topic Data matching in QlikView

Data matching

Tue, 22 Jun 2010 13:16:38 GMT

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

KXRRR

02/02/2010 00:00:00

KXRRR

03/02/2010 00:00:00

132.87

112.21

KX116

04/02/2010 00:00:00

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

Data matching

Tue, 22 Jun 2010 13:41:12 GMT

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.

Data matching

Tue, 22 Jun 2010 16:12:05 GMT

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

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"

Data matching

Tue, 22 Jun 2010 16:29:56 GMT

So, you can do this:

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

Data matching

Tue, 22 Jun 2010 17:16:07 GMT

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.

Data matching

Tue, 22 Jun 2010 17:41:55 GMT

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.

Data matching

Thu, 18 Nov 2010 09:09:07 GMT

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

what would be the solution.

Thanks,

Data matching

Thu, 18 Nov 2010 15:30:41 GMT

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