topic Re: Row iteration counter in Qlik Sense with condition in App Development

Row iteration counter in Qlik Sense with condition

bepriba — Tue, 19 Apr 2022 14:27:43 GMT

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

Re: Row iteration counter in Qlik Sense with condition

marcus_sommer — Tue, 19 Apr 2022 14:45:58 GMT

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

Re: Row iteration counter in Qlik Sense with condition

canerkan — Tue, 19 Apr 2022 14:58:58 GMT

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

Re: Row iteration counter in Qlik Sense with condition

bepriba — Tue, 19 Apr 2022 15:41:09 GMT

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