topic Re: Re: Not Exists - Object Level in QlikView

Not Exists - Object Level

Anonymous — Fri, 22 Nov 2013 17:40:10 GMT

I have a column Clients in the Fact table and there's another list of Clients which is ExClients provided in Excel file. I need to show in one tab on the front-end the list which should be Clients - ExClients i.e. those Clients that do not exist in ExClients.

If it was across the board, I could have used Not Exists in script but need to keep the original Clients' list as it and only show the subtracted list on one object/tab.

Any suggestions?

Re: Not Exists - Object Level

MayilVahanan — Fri, 22 Nov 2013 17:52:56 GMT

Are you looking like this?

Re: Not Exists - Object Level

fred_s — Fri, 22 Nov 2013 17:59:09 GMT

With the load of Ex-Clients, you could add an extra field like this:

ex:

Load * ,
'1' as Ex
;

Load *
FROM Ex-Clients;

and in your table use "Ex <> 1".

Re: Not Exists - Object Level

Clever_Anjos — Fri, 22 Nov 2013 19:13:39 GMT

1) Create a flag into Fact Table

2) Using the OnActivate Sheet trigger set a selection using Select in Field action

Re: Not Exists - Object Level

Anonymous — Fri, 22 Nov 2013 20:53:38 GMT

Clever Anjos,

I was thinking along the same lines as Mayil Vahanan Ramasamy and Fred Schonenberg but could you elaborate your approach ?

Re: Re: Not Exists - Object Level

fred_s — Sat, 23 Nov 2013 16:03:58 GMT

Hi Arsalan,

See attachment...

This way you can select Ex Clients with Ex = 1 or Ex <> 1 for the Non-Ex-Clients 🙂

Re: Re: Not Exists - Object Level

Clever_Anjos — Sat, 23 Nov 2013 19:09:18 GMT

You can use as Fred Schonenberg suggested, or like this

ExClient:
LOAD * Inline
[
ClientID,Client1
1,XX
2,YY
];
Test:
LOAD 
*,  
Exists(Client1,Client) as Flag
Inline
[
ClientID,Client
1,XX
2,YY
3,ZZ
4,BB
];

doing this you can

count({1<Flah={"-1"}>}Client) count clients that are present into ExClient

count({1<Flah-={"-1"}>}Client) count clients that are NOT present into ExClient

CliCl iententClClien