Counter creation

[Lou_Lab]

Hello everyone,

I need some help, I have a table in the form customer / product / year / month / quantity. I'd like to add an ID column with a chrono number made up of the year, of the month and a counter that resets to 0 each month (like 202310001, 202310002...).

I have the beginning of the formula, but I don't know how to reset the counter to 0 each month: [Date.Master_Calendar.Year]&Num([Date.Master_Calendar.Month],'00')&Num(RowNo(Total),'000')

Thanks !

[Albarosæ]

Hi,

For counting in load scripts, you can use peek(). You can try the script below:

Temp:
Load
	[Date.Master_Calendar.Year],[Date.Master_Calendar.Month],Customer,Product,Quantity,
	IF(Date.Master_Calendar.Year=Peek(Date.Master_Calendar.Year) and [Date.Master_Calendar.Month] = peek([Date.Master_Calendar.Month]),
		peek(Counter)+1,
        0
	) as Counter
Resident Data;
Drop Table Data;

Fin:
Load
*,
[Date.Master_Calendar.Year]&[Date.Master_Calendar.Month]&'00'&Counter as ID
Resident Temp;
Drop Table Temp;

[Lou_Lab]

Hello,

thanks for your answer but I'm not an admin in company so I don't have access to the script options... I would like to do it as a measure if it's possible.

[Albarosæ]

Hello, 

Maybe something like this as a measure:

[Date.Master_Calendar.Year]&[Date.Master_Calendar.Month]&'00'&Aggr(RowNo(),[Date.Master_Calendar.Year],[Date.Master_Calendar.Month],Customer,Product,Quantity)

Note: I got the idea from below post:

https://community.qlik.com/t5/QlikView-App-Dev/how-to-make-rowno-with-aggr-for-grouping-data/m-p/141...