Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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;
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.
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: