How can I filter data matched on 2 criteria where for 1 criteria there is a restriction?

[a_ligtvoet]

Hi,

I have a lot of data available from our Warehouse management system, so I want to build in some criteria in the scripting.

Below a short example (I hope it's clear)

Client, Action, Location, Quantity

A, D, IN123, 1

A, E, OUT123, 2

A, F, IN123, 3

A, D, MIX123, 4

A, E, IN123, 5

B, D, IN123, 6

B, E, IN123, 7

B, F, OUT123, 8

C, D, FAR123, 9

C, E, MIX123, 10

C, F, IN123, 11

At first I only needed the data for Action D for customers A and C. I got that by using this:

Load  ...

          ...

where match(Client,'A','C')

AND Action = 'D';

This returns the records with quantity 1, 4 and 9

Now I found out people are not always performing the correct action, so now I also have to add action E but with the restriction that the Location starts with IN.... This restriction does NOT apply for action D.

So I want to get the records returned with quantity 1, 4, 5 and 9

I can use match(Action,'D','E'), but than I get all data for action E (I don't want the records with quantity 2 and 10).

I can then add where left(Location,2)<>'IN', but than I will also miss data for action D from Location starting with IN... (I will miss records with quantity 4 and 9)

Can someone please help me?

[sunny_talwar · Accepted solution]

May be this:

Where Match(Client,'A','C') and (Action = 'D' or (Action = 'E' and WildMatch(Location, 'IN*')))

[a_ligtvoet]

Thanks Sunny,

that's what I was looking for!