Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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"
So, you can do this:
if(wildmatch('klb','*E*','*L*','*B*','*M*','*') > 0, 0, "Rostered Pay Value"))
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.
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.
If I want January, fex instead of 1, 2 etc..
what would be the solution.
Thanks,