Can I get some help with adding Group ID?

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.

Sample.xlsx

Output

Transformation

Accepted answers

NeilParrott

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

GroupID_continuousFormat.yxmd

All comments

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.

workflow.yxmd

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.

workflow.yxmd

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

GroupID_continuousFormat.yxmd

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

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

GroupID_continuousFormat.yxmd

MROGK

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

NeilParrott

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703