topic Re: Exclude record during Load in QlikView

Exclude record during Load

martynlloyd — Mon, 19 Jan 2015 17:19:16 GMT

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.

Re: Exclude record during Load

Mon, 19 Jan 2015 17:21:13 GMT

Hi Martin,

Where Not KEY1 ='Action 2'

should be fine? Unless I am missing something

hope that helps

Joe

Re: Exclude record during Load

martynlloyd — Mon, 19 Jan 2015 17:38:33 GMT

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

Re: Exclude record during Load

Anonymous — Mon, 19 Jan 2015 17:48:56 GMT

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

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

Re: Exclude record during Load

Mon, 19 Jan 2015 17:57:22 GMT

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

Re: Exclude record during Load

cwolf — Mon, 19 Jan 2015 18:04:58 GMT

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