topic Re: Data Island Exclusion set analysis in QlikView

Data Island Exclusion set analysis

gmenoutis — Wed, 16 May 2018 12:27:37 GMT

I have a customers table, with [Customer Code] being the linking field, and a transactions table which among others includes [Transaction Store Code] and [Customer Code].

I also have made a data island which includes the field [NOT Transaction Store Code]. The point is to have a table with the customer list such that when some [NOT Transaction Store Code]s are selected, the [Customer Code]s which have some transactions on these stores are excluded.

I have thus created the following calculated dimension:

aggr(only({<[Transaction Store Code]-=[NOT Transaction Store Code]>} [Customer Code]),[Customer Code])

and set it to not show nulls.

This thing indeed does what I want. Unfortunately, it also hides [Cutomer Code]s which have no transactions at all. Is there any way to get around this?

Re: Data Island Exclusion set analysis

sunny_talwar — Wed, 16 May 2018 12:31:14 GMT

You mean those rows where Transaction Store Code is null?

Re: Data Island Exclusion set analysis

gmenoutis — Wed, 16 May 2018 12:35:49 GMT

I am not very sure how to answer this. There are no rows in the transactions table where [Transaction Store Code] is null; BUT, there are rows in the customer table for which there are no transactions with such a [Customer Code].

Re: Data Island Exclusion set analysis

sunny_talwar — Wed, 16 May 2018 12:41:20 GMT

That makes sense, so essentially the data is missing for certain Customer Code. Not sure if this will work or not, but can you try this

Aggr(Only({<[Transaction Store Code] = e([NOT Transaction Store Code])>} [Customer Code]), [Customer Code])

Re: Data Island Exclusion set analysis

gmenoutis — Wed, 16 May 2018 12:46:08 GMT

It doesn't work, same problem

Re: Data Island Exclusion set analysis

sunny_talwar — Wed, 16 May 2018 13:19:17 GMT

Would you be able to share a sample to try out few things?

Re: Data Island Exclusion set analysis

gmenoutis — Wed, 16 May 2018 13:20:58 GMT

I am creating a simple warehouse with sample data for this purpose. After I test a few alternatives I'll post it here.

Re: Data Island Exclusion set analysis

sunny_talwar — Wed, 16 May 2018 13:28:05 GMT

Sounds good.

Re: Data Island Exclusion set analysis

gmenoutis — Wed, 16 May 2018 13:44:51 GMT

Here are the specs:

When no [NOT Transaction Store Code]s are selected, the target calculated dimension should return all [Customer Code]s: C1,C2,C3

When [NOT Transaction Store Code] has S1 selected, this leads to [TranID]s T1,T2,T3, which in turn lead to [Customer Code]s C1,C2. These must be excluded, so I should only get C3. This case is also identical to selecting both S1,S2 as the [Customer Code]s implied are the same.

When [NOT Transaction Store Code] has S2 selected, this leads to [TranID] T4, which in turn leads to [Customer Code] C2. This must be excluded, so I should only get C1,C3.

...and here is the code:

customers:

Load * inline

[

Customer Code

];

transactions:

Load * inline

[

TranID,Transaction Store Code,Customer Code,Price

T1,S1,C1,50

T2,S1,C1,40

T3,S1,C2,30

T4,S2,C2,60

];

negateStore:

Load * inline

[

NOT Transaction Store Code

];

Re: Data Island Exclusion set analysis

sunny_talwar — Wed, 16 May 2018 13:51:55 GMT

Try this

=Aggr(Only({<[Transaction Store Code] = e([NOT Transaction Store Code])>/<Flag = {1}>} [Customer Code]), [Customer Code])

Where Flag is created in the customer table like this

customers:

LOAD [Customer Code],

1 as Flag;

LOAD * INLINE [

Customer Code

];

transactions:

LOAD * INLINE [

TranID, Transaction Store Code, Customer Code, Price

T1, S1, C1, 50

T2, S1, C1, 40

T3, S1, C2, 30

T4, S2, C2, 60

];

negateStore:

LOAD * INLINE [

NOT Transaction Store Code

];

Re: Data Island Exclusion set analysis

gmenoutis — Wed, 16 May 2018 13:57:26 GMT

Although it seems to work for most cases, it doesn't work for selecting both S1 and S2: instead of C3 it filters nothing and shows C1,C2,C3.

Re: Data Island Exclusion set analysis

sunny_talwar — Wed, 16 May 2018 14:05:08 GMT

Try this

=Aggr(If(GetSelectedCount([NOT Transaction Store Code]) = 0, [Customer Code], Only({<Flag = {1}>-<[Transaction Store Code] = p([NOT Transaction Store Code])>} [Customer Code])), [Customer Code])

Re: Data Island Exclusion set analysis

gmenoutis — Wed, 16 May 2018 14:24:19 GMT

Well, this works! Thank you very much!

Could you please elaborate how it works? Some questions I can produce are the following:

1) Why is the Flag=1 modifier needed? I actually notice that I can change this to $ or 1 with the same results.

2) Why is the p needed, whereas the following doesn't work: [Transaction Store Code] -= [NOT Transaction Store Code]

3) Why is the if getselectedcount needed?

I hope I can get some deeper understanding of this.

Re: Data Island Exclusion set analysis

sunny_talwar — Wed, 16 May 2018 14:30:26 GMT

1) Having thought about it a little more, you don't really need Flag... you can use this

=Aggr(If(GetSelectedCount([NOT Transaction Store Code]) = 0, [Customer Code], Only({<[Customer Code] = {"*"}>-<[Transaction Store Code] = p([NOT Transaction Store Code])>} [Customer Code])), [Customer Code])

2) p() is giving possible value and we are using - to remove that from the set of all Customer Codes... see the negative sign in red

4) We need the GetSelectedCount() because when nothing is selected, all values are possible within [NOT Transaction Store Code] which is equivalent of selecting both S1 and S2. In order to differentiate between when both are selected vs when nothing is selected... we use an if statement

When no [NOT Transaction Store Code] is selected, just use the simple Customer Code as dimension... else use Only({<[Customer Code] = {"*"}>-<[Transaction Store Code] = p([NOT Transaction Store Code])>} [Customer Code])

Re: Data Island Exclusion set analysis

gmenoutis — Wed, 16 May 2018 14:32:17 GMT

Nice, thank you again for your insight!

Actually, for future reference, it seems I can get rid of the if, if I don't use p():

=Aggr(

Only({$-<[Transaction Store Code] = [NOT Transaction Store Code]>} [Customer Code])

, [Customer Code])

This makes it also easier for me to add more exclusions if needed