Row iteration counter in Qlik Sense with condition

[bepriba]

Hi, I would need to implement a row iteration counter but with conditions. I've tried using AutoNumber() but it doesn't seem to really work.

This is the data that I have :

ID	CONDITION
ABC123	TRUE
ABC123	FALSE
ABC456	TRUE
ABC456	TRUE
ABC456	FALSE
ABC456	FALSE
ABC789	TRUE
ABC789	TRUE
ABC789	TRUE
ABC789	FALSE
ABC789	FALSE
ABC789	FALSE

I wanted to achieve something like this :

ID	CONDITION	COUNTER
ABC123	TRUE	1
ABC123	FALSE	1
ABC456	TRUE	1
ABC456	TRUE	2
ABC456	FALSE	1
ABC456	FALSE	2
ABC789	TRUE	1
ABC789	TRUE	2
ABC789	TRUE	3
ABC789	FALSE	1
ABC789	FALSE	2
ABC789	FALSE	3

Any help is appreciated!

Thanks

[marcus_sommer · Accepted solution]

Maybe with interrecord-functions and something like this:

t1: load *, ID & '|' & CONDITION as KEY, recno() as RECNO from SOURCE;

t2: load *, if(KEY = previous(KEY), peek('COUNTER') + 1, 1) as COUNTER
       resident t1 order by ID, RECNO;

- Marcus

[canerkan]

Hi Bepriba,

you could try something like this in your load script,:

//Counting Occurrence of each Condition for each ID
tmp:
Load
ID,
CONDITION,
Count(CONDITION) as CountCondition
From....
Group by ID, Condition;

//Using IterNo() to increase Count by 1 and generating new dataset for each occurrence
Final:
Load
ID,
CONDITION,
IterNo() as COUNTER
Resident tmp
while IterNo() <= CountCondition;

Let me know if it helped.

Regards,

Can

[bepriba]

Hi Marcus,

Thanks a lot, this worked like a charm with a slight adjustment : 

• I used order by KEY instead of order by ID, RECNO