Exclude All except some

[wcilliers]

Hi All,

I have two scenarios that I need help with.

Scenario 1:

I need to exclude billing types A and B, but only from 01/04/2017, all transactions before that still has to be included. Preferably, I dont want to load twice if it is not necessary, was hope there is an easy way to structure the where clause....

Scenario 2:

I need to exclude all ITEMCODES starting with ABC, except ABC1, ABC4, ABC12.

Again, I would like to do this in a where clause if possible.

Any help would be greatly appreciated.

Thanks,

Wynand

[Anil_Babu_Samineni]

Scenario-1:

May be this?

Load * From Table Where Not Match([billing types],'A', 'B') and Transactions = '01-04-2017';

or

Load * From Table Where [billing types] <> 'A' and [billing types] <> 'B' and Transactions = '01-04-2017';

Scenario-2:

You need except ABC1 ... But, You have starting with ABC only. Would you provide some inputs may be data and tell us result you want to see

[tamilarasu · Accepted solution]

Hi Wynad,

Try something like below..

Scenario 1:

Load *

From

Source

Where (Date >= '01/04/2017' And Not Match([Billing Type],'A','B') ) OR (Date <'01/04/2017') ;

Scenario 2:

Load *

From

Source

Where Wildmatch(ITEMCODES, 'ABC*) And Not Match(ITEMCODES,'ABC1','ABC4','ABC12');

[wcilliers]

Hi Tamil,

Thank you.

Scenario 1:

I added the OR part which forgot to add initially and it now works. Thanks!

Scenario 2:

Has to be the other way around.

I swapped it around a bit and used your logic from Scenario 1 and it works...

Where

Match(ItemCode,'ABC1','ABC4','ABC12')

OR

NOT Wildmatch(ItemCode, 'ABC*')

Thanks for the help All!!

Much Appreciated!

[tamilarasu]

Great Wynand!! Have a good day!