Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count

Hi,

I need some help.

Here is my datas:

error loading image

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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.

View solution in original post

5 Replies
Anonymous
Not applicable
Author

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.

Not applicable
Author

It works great

Thanks a lot

Not applicable
Author

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
Not applicable
Author

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
Atoc11
Atic22
Atuc33
Bfgt41
Cgsd51


Not applicable
Author

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