Count Distinct

[#1]

Hi!

I need to know when a certain field value (Pnr) for the first time arise depending on the date in the same table (not depending on the Load Order, the table could be unsort) . I have a table that looks like this:

Day	Pnr
20090301	19771231
20090315	19681130
20090320	19400506
20090328	19771231
20090402	19990102
20090412	19820401
20090415	19681130
20090421	19400506
20090430	19990102
20090501	19990102
20090505	19610501
20090510	19820401
20090523	19771231
20090524	19681130
20090528	19540304
20090603	19610501
20090614	19820401
20090619	19681130
20090626	19400506
20090629	19740921

I like to create a field number that can give me the number 1 and so on in the row where the distinct Pnr for the first time appear depending on date. For instance i like to return this table like this when i load it in the script:

Day	Pnr	FirstTime
20090301	19771231	1
20090315	19681130	1
20090320	19400506	1
20090328	19771231	2
20090402	19990102	1
20090412	19820401	1
20090415	19681130	2
20090421	19400506	2
20090430	19990102	2
20090501	19990102	3
20090505	19610501	1
20090510	19820401	2
20090523	19771231	3
20090524	19681130	3
20090528	19540304	1
20090603	19610501	2
20090614	19820401	3
20090619	19681130	3
20090626	19400506	3
20090629	19740921	1

Best regards
Kristofer

[prasad]

Hi,

Really need to specify what you are asking, may be try like this to achieve it.

Load

Day as [Day],

Pnr as []Pnr],

1 as [FirstTime Count]

;

Try this script load the data, and open the pivot table, use the FirstTime count in expression. Maybe this works for you. Thanks!

Regards,

Prasad.

[#3]

Hi!

That will not work for me because it will give me the value 1 for all rows in the table. You could say that i would like to use a function like Rank() for the field values Pnr and Day together. I would like to set value 1 for the first row with the Pnr depending on the first date (day), and so on.

Day	Pnr	Rank
20090320	19400506	1
20090421	19400506	2
20090626	19400506	3
20090528	19540304	1
20090505	19610501	1
20090603	19610501	2
20090315	19681130	1
20090415	19681130	2
20090524	19681130	3
20090619	19681130	4
20090629	19740921	1
20090301	19771231	1
20090328	19771231	2
20090523	19771231	3
20090412	19820401	1
20090510	19820401	2
20090614	19820401	3
20090402	19990102	1
20090430	19990102	2
20090501	19990102	3

/Kristofer

[Anonymous · Accepted solution]

Hi Kristofer,

Here's one way of doing it. Basically I load the unsorted table and sort it based on Pnr and Day ascending. This way I can look at each row and if the Pnr is the same I increase the count by one until the Pnr changes at which point I reset the count to 1.

T1:
LOAD
date#(Day,'YYYYMMDD') as Day,
Pnr
FROM
C:\Users\Johannes\Documents\SupportTesting\SortPeek\Data.xls (biff, embedded labels, table is Sheet1$);

Load
Day,
Pnr,
if(Pnr=peek('Pnr'),peek('FirstTime')+1,1) as FirstTime
resident T1
order by Pnr, Day asc;

drop table T1;

[#5]

Thanx, it worked perfect.

[#6]

I have another aspect of this problem. If I have another field value that give me the information if the row is annul (see table below -1 means it annuled). I would like to skip to give these row a FirstTime value and also the row that should be annuel (exact same values exempt for the value 1 in the field annul) and count the rows with only annul 1.

Now it looks like this:

Period	Day	Time	VisitDay	Pnr	Typ	FirstTime	Annul
200903	20090301	12:20:34	20090301 12:20:34	19771231	A	1	1
200903	20090312	15:23:00	20090312 15:23:00	19681130	B	1	1
200903	20090314	08:12:40	20090314 08:12:40	19400506	A	1	1
200903	20090329	10:24:25	20090329 10:24:25	19990102	B	1	1
200904	20090412	13:20:10	20090412 13:20:10	19990102	C	2	1
200904	20090416	11:20:45	20090416 11:20:45	19820401	E	1	1
200904	20090416	11:20:45	20090416 11:20:45	19820401	E	1	-1
200904	20090416	11:20:45	20090416 11:20:45	19820401	E	2	1
200904	20090416	11:20:45	20090416 11:20:45	19820401	E	2	-1
200904	20090420	14:05:20	20090420 14:05:20	19400506	C	2	1
200904	20090423	17:30:40	20090423 17:30:40	19990102	B	3	1
200905	20090502	07:58:00	20090502 07:58:00	19990102	B	4	1
200905	20090519	11:10:30	20090519 11:10:30	19610501	A	1	1
200905	20090513	12:40:11	20090513 12:40:11	19820401	D	3	1
200905	20090517	15:23:40	20090517 15:23:40	19771231	A	2	1
200905	20090520	11:34:12	20090520 11:34:12	19681130	E	2	1
200905	20090504	09:07:50	20090504 09:07:50	19540304	A	1	1
200906	20090504	09:07:50	20090504 09:07:50	19540304	A	1	-1
200905	20090504	09:07:50	20090504 09:07:50	19540304	A	2	1
200906	20090504	09:07:50	20090504 09:07:50	19540304	A	2	-1
200906	20090609	12:45:00	20090609 12:45:00	19820401	F	4	1
200906	20090610	15:23:34	20090610 15:23:34	19681130	F	3	1
200906	20090625	17:07:20	20090625 17:07:20	19400506	D	3	1
200906	20090625	17:07:20	20090625 17:07:20	19400506	D	3	-1
200906	20090625	17:07:20	20090625 17:07:20	19400506	D	4	1
200906	20090625	17:07:20	20090625 17:07:20	19400506	D	4	-1
200907	20090706	16:04:15	20090706 16:04:15	19771231	A	3	1
200907	20090717	14:07:40	20090717 14:07:40	19681130	E	4	1
200907	20090721	10:32:23	20090721 10:32:23	19820401	D	5	1
200907	20090729	13:00:40	20090729 13:00:40	19820401	F	6	1

It should look like this:

Period	Day	Time	VisitDay	Pnr	Typ	FirstTime	Annul
200903	20090301	12:20:34	20090301 12:20:34	19771231	A	1	1
200903	20090312	15:23:00	20090312 15:23:00	19681130	B	1	1
200903	20090314	08:12:40	20090314 08:12:40	19400506	A	1	1
200903	20090329	10:24:25	20090329 10:24:25	19990102	B	1	1
200904	20090412	13:20:10	20090412 13:20:10	19990102	C	2	1
200904	20090416	11:20:45	20090416 11:20:45	19820401	E		1
200904	20090416	11:20:45	20090416 11:20:45	19820401	E		-1
200904	20090420	14:05:20	20090420 14:05:20	19400506	C	2	1
200904	20090423	17:30:40	20090423 17:30:40	19990102	B	3	1
200905	20090502	07:58:00	20090502 07:58:00	19990102	B	4	1
200905	20090519	11:10:30	20090519 11:10:30	19610501	A	1	1
200905	20090513	12:40:11	20090513 12:40:11	19820401	D	1	1
200905	20090517	15:23:40	20090517 15:23:40	19771231	A	2	1
200905	20090520	11:34:12	20090520 11:34:12	19681130	E	2	1
200905	20090504	09:07:50	20090504 09:07:50	19540304	A		1
200905	20090504	09:07:50	20090504 09:07:50	19540304	A		-1
200906	20090609	12:45:00	20090609 12:45:00	19820401	F	2	1
200906	20090610	15:23:34	20090610 15:23:34	19681130	F	3	1
200906	20090625	17:07:20	20090625 17:07:20	19400506	D		1
200906	20090625	17:07:20	20090625 17:07:20	19400506	D		-1
200907	20090706	16:04:15	20090706 16:04:15	19771231	A	3	1
200907	20090717	14:07:40	20090717 14:07:40	19681130	E	4	1
200907	20090721	10:32:23	20090721 10:32:23	19820401	D	3	1
200907	20090729	13:00:40	20090729 13:00:40	19820401	F	4	1

The script looks like this:

T1:
LOAD
Period,
Day,
Time,
date#(Day,'YYYYMMDD') &' '& timestamp(Time, 'hh:mm:ss') as VisitDay,
Pnr,
Typ,
Annul
FROM
test.xls (biff, embedded labels, table is Sheet1$);

left join
Load
date#(Day,'YYYYMMDD') &' '& timestamp(Time, 'hh:mm:ss') as VisitDay,
Pnr,
if(Pnr=peek('Pnr'),peek('FirstTime')+1,1) as FirstTime
resident T1
order by Pnr, VisitDay asc;

[#7]

Is there anyone out there that can help me with the problem above or just a suggestion that I could work with, I haven't a clue.