Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Hi Marcus,
Thanks a lot, this worked like a charm with a slight adjustment :