Count

[#1]

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

RG

[Anonymous · Accepted solution]

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.

[#3]

It works great

Thanks a lot

[#4]

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

RG

[Anonymous]

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

[#6]

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

RG