If condition with strings and date

[AdrianaFerro]

Hello everyone,

I need to calculate the maximum of an if condition per 2 ID columns:

Maximum of if ((issueorigin=‘completed’) and (changeto=‘In Progress’) and (changefield=‘status’) then, maximum(changecreation - date column) per issueid and sprintid) per (issueid and sprintid)

Any suggestion on how can I perform this?

Thank you!

[rolandnemeth]

Hi @AdrianaFerro,

could you upload the workflow, what you tried to apply?

Is it possible to upload the initial table and the result table, what you want to create from the init?

Best Regards,
Roland

[Iris]

I think you have two options

1. add everything into a single /Column Expressions , they are extremely powerful.
2. generate multiple helper columns and solve it step by step.

Option one is easier to generate, I would still vote for option 2. Option two is the one which will make it a lot easier to maintain.

[AdrianaFerro]

Hi @Iris thank you for your suggestions, I like your number 2 solution but don’t even know where to start…

[Iris]

@AdrianaFerro do you think you can generate us some example data? That would be very helpful for generating the workflow with you.

[AdrianaFerro]

Hi @Iris so glad for your help.

I’m sharing some example data

Thank you again!

Example data.xlsx (149.1 KB)

[mlauber71]

@AdrianaFerro you might want to take an initial look at this:

remove duplicates with condition and add the last date – mlauber71

Please note the H2 driver has been updated in order to allow Window functions.

[Thyme]

Hi @AdrianaFerro,

your sample doesn’t contain a column “datecolumn” and no row meets all 3 conditions. I will write down the general logic as I interpret your question:

1. Rule-Based Row Filter to keep only rows that meet all 3 criteria
2. calculate Max(changecreation, datecolumn) or (changecreation - datecolumn). It’s not clear to me which one is desired.
3. GroupBy with issueid and sprintid as group columns, aggregate calculated column from step 2 with Max()-Function.

Mathematical operations depend on the data type. Dates that have a nice string format can use string comparison, otherwise casting to other data types is required.

Happy New Year, T

[AdrianaFerro]

Morning @Thyme, you are right, next time I need to be more careful in the examples that I share.

I will try your approach and thank you

Wish you a wonderful 2022

[mlauber71]

@AdrianaFerro happy new year. Did you have a chance to check out the solutions I have built around your data; mabye you can adapt that:

[AdrianaFerro]

@mlauber71 yes I did, the solution was part of your reply plus the one provided by @Thyme

Thank you a lot