Exclude record during Load

[martynlloyd]

Hi Everyone,

I have a table with multiple keys:

KEY1     KEY2

Action 1, Event 1, Date 1

Action 1, Event 2, Date 2

Action 2, Event 3, Date 3

I have a spreadsheet with a list of actions to be excluded, for example I want to exclude Action 2 from the Load.

Because KEY1 is repeated, I cannot use where not exists, as I will only get the first occurrence of that key.

Anyone have an elegant solution for this?

Regards,

Marty.

[#2]

Hi Martin,

Where Not KEY1 ='Action 2'

should be fine? Unless I am missing something

hope that helps

Joe

[martynlloyd]

Too simplistic I'm afraid - has to be dynamic, not hard-coded...

[Anonymous]

Is "simplistic" a derogatory term for "simple"?   Simple is good

OK, you need to provide a little more information about your issue.

[#5]

Sorry think I see what you mean after some further thought.

You want to concat a distinct list of actions from your spread sheet (comma separated and single quotes around each), peek and store this as a variable, then use that variable as the basis for a not match where clause

Where Not Match(KEY1, vActionListvariable)

hope that helps

Joe

[cwolf · Accepted solution]

Hi Martyn,

you have to use different field names for your exclusion list and your load statement. For example:

ExcludedKeys:

Load

Key1 as ExclKey

From ....

Table:

Load * From ...

Where not Exists(ExclKey,Key1);

Regards

Christian