topic Count in QlikView

Count

Thu, 26 Nov 2009 13:16:41 GMT

Hi,

I need some help.

Here is my datas:

I would like to count each couple ID/INF individually and store it into a qvd specific column

For example,

DDD/A1= 1

CCC/A2=2

AAA/A1 =3

AAA/A2=2

I try this:

(ID=previous(ID) and INFprevious(INF),peek(Nb)+1,Nb) // Nb as counter

but I only have results '1' or '2' because Nb is not really stored

Do you have any idea please ?

Best regards

Count

Anonymous — Thu, 26 Nov 2009 13:44:12 GMT

Hi,

Here's an example on how to load the count for every unique combination of ID and INF:

T1:
LOAD * INLINE [
ID, INF, NB
AAA, A1, 1
AAA, A1, 2
AAA, A1, 3
AAA, A2, 2
AAA, A2, 1
AAA, A3, 2
AAA, A3, 1
BBB, A1, 2
BBB, A2, 1
BBB, A2, 2
BBB, A3, 1
CCC, A1, 1
CCC, A1, 2
DDD, A1, 2
];

Load
ID & '/' & INF as Identifier,
count(ID & '/' & INF) as Counter
resident T1
group by ID, INF;

Identifier will have the form of AAA/A1 while the Counter will be the number of occurences in the source table.

Count

Thu, 26 Nov 2009 14:06:33 GMT

It works great

Thanks a lot

Count

Thu, 26 Nov 2009 15:54:57 GMT

to continue....

I have LOAD INLINE with

ID INF

A toc

A tic

A tuc

B fgt

C gsd

I need to create a new row, with autoincrement, with reinitialisation for each new ID

For example:

ID INF Number

A toc 1

A tic 2

A tuc 3

B fgt 1

C gsd 1

Do you have any idea please ?

Best regards

Count

Anonymous — Thu, 26 Nov 2009 16:18:37 GMT

Here's an example:

T1:
Load
*,
rowno(),
if(peek('ID')<>ID,1, peek('Incremental')+1) as Incremental;
Load * Inline [
ID, INF
A, toc
A, tic
A, tuc
B, fgt
C, gsd
];

The preceeding load statement can also be done as a resident load as in the previous example. Just showing another possibility. Key for this to work is that the table containing ID is sorted by ID. If it goes A, B, A, A, C then the incremental numbers will start over when switching between ID values. So, if it's not sorted look into the Order By functionality of the load statement in the F1 help or Reference manual.

ID	INF	rowno()	Incremental
A	toc	1	1
A	tic	2	2
A	tuc	3	3
B	fgt	4	1
C	gsd	5	1

Count

Thu, 26 Nov 2009 16:41:09 GMT

it works too

I'd tried peek function but I didn't work because i'd put aliases to ID/INC fields !!

Thanks a lot