topic Re: How to display add/remove and count of id? in QlikView

How to display add/remove and count of id?

silambarasan — Tue, 24 May 2016 03:13:45 GMT

Day-1:

ID,Date

A, 1-jan-2016

B,1-jan-2016

C,1-jan-2016

D,1-jan-2016

Day-2

ID,Date

A, 2-jan-2016

B,2-jan-2016

C,2-jan-2016

E,2-jan-2016

I want three tables

Table one display count of id on daily basis.

Table two display newly added id on daily basis

Table three display removed id.

table-1

MonthYear	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
Jan-2016	4	4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Feb-2016	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Mar-2016	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Apr-2016	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
May-2016	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

if i click 2-jan in table-1 ,it will show list of new id added and list of existing id removed in two separate table

Eg:

Day-2

table-2

ID	newtype
E	new

table-3

ID	newtype
D	remove

johnw‌QlikView App Development‌hic‌gwassenaar‌swuehl‌

Message was edited by: Silambarasan M

Re: How to display add/remove and count of id?

sunny_talwar — Tue, 24 May 2016 03:30:56 GMT

Still trying to think what the best way would be to show the removed, but check out the first two tables in the attached application

Re: How to display add/remove and count of id?

sujeetsingh — Tue, 24 May 2016 03:35:02 GMT

Just do one thing is that join all the three tables in your model with date + ID as key.Now maintain three columns as ID from First table as ID_Pre, ID from second table as ID_Post.

Then you can derive a logic as IF ID_Pre<>ID_Post then 1 as ID Added some thing like that ...

Re: How to display add/remove and count of id?

silambarasan — Tue, 24 May 2016 03:37:54 GMT

Thanks for your help,same thing i did.I am also looking to find the removed id

Re: How to display add/remove and count of id?

silambarasan — Tue, 24 May 2016 03:41:46 GMT

could you please attach qvw file

Re: How to display add/remove and count of id?

qliksus — Wed, 25 May 2016 05:38:23 GMT

Are you looking for something like this

When you select 01/02/2016

When you select 01/03/2016

New Expression : (Count({1<Date={'$(=only(Date))'}>} DISTINCT Date) and count({1} DISTINCT Date)=1 )

Removed Exp : (Count({1<Date={'$(=only(Date))'}>} DISTINCT Date)=0 and count({1<Date={"<$(=only(Date))"}>} DISTINCT Date)>=1 )

Re: How to display add/remove and count of id?

silambarasan — Wed, 25 May 2016 12:32:32 GMT

In your Removed Expression you shows all the removed ID till date but I want to know removed id from previous date not before than yesterday.

Re: How to display add/remove and count of id?

qliksus — Thu, 26 May 2016 02:46:56 GMT

Just change the Remove Expression as below

Removed Exp : (Count({1<Date={'$(=only(Date))'}>} DISTINCT Date)=0 and count({1<Date={'$(=date(only(Date)-1))'}>} DISTINCT Date)>=1 )

Re: How to display add/remove and count of id?

silambarasan — Fri, 27 May 2016 06:28:44 GMT

qliksus please help..how to do this in back end scripting not in front end expression

Re: How to display add/remove and count of id?

silambarasan — Fri, 27 May 2016 06:33:16 GMT

How to find removed id?i am waiting for your response