Can I get some help with adding Group ID?

[MROGK]

I'd like to group the data by contiguous dates which have '0' production. If the 1/1/2022 & 1/2/2022 have '0' production and since are continuous will have same group ID. If 1/3/2022 has some production value will have a different group ID. I used multi Row formula with date time difference and production !=0 ..doesn't seem to work. 

Attached sample data file with output example as well. 

Thanks.

[JagdeeshN]

@MROGK  can you try this formula in your multi-row tool.

if isnull([Row-1:Production]) or isempty([Row-1:Production]) then 1
elseif [Row-1:Production]=[Production] then [Row-1:GroupID]
else [Row-1:GroupID]+1
endif

This assigns a new GroupID every time the Production value changes. It initiates with the value 1.

Hope this helps.

[MROGK]

@JagdeeshN . Thanks for taking a look. The approach you proposed does not seem take continuous dates into consideration. The below table should have a different group ID's for 10/20/2021 & 09/27/2021. And for 09/05/2021 & 09/04/2021 it should have one group ID as they are continuous and have '0' production.

[JagdeeshN]

@MROGK 

I missed that in your earlier post.

Please try the below formula:-

if isnull([Row-1:Production]) or isempty([Row-1:Production]) then 1
elseif [Row-1:Production]=[Production] and [Date]=[Row-1:Date] then [Row-1:GroupID]
else [Row-1:GroupID]+1
endif

This takes into account the date while assigning the GroupId's.

[NeilParrott]

Hi @MROGK ,

Take a look at this once you get a chance. I believe this can solve the question you are working on. It does include a couple extra columns that you may need not, but that was to help with validation.

Hope this helps!

Neil

[MROGK]

@JagdeeshN .. still see an issue. The data 12/25 & 12/26 both have '0' production but have different group ID. I'm looking for same ID for dates that are contiguous. Below is the output I'm looking to see. 

[MROGK]

@NeilParrott ..I cannot download your solution, says no file found. Can you reload ? Thanks.

[NeilParrott · Accepted solution]

Trying to reload file. @MROGK let me know if it still gives you an issue.

[MROGK]

@NeilParrott .. Thank you very much. The solution you proposed worked.

[NeilParrott]

@MROGK Awesome. I'm glad it works out!