Min and Max Values - Row

[JDong]

Hi Team,

I have quarterwise data as shown below

I have transformed the data is the below format using a flow

Office A	1Q	0	0
Office A	2Q	0.25	0.24
Office A	3Q	0.44	0.52
Office A	4Q	0.85	1.01
Office A	5Q	1.74	1.09
Office A	6Q	2.15	1.47
Office B	1Q	0.41	0.08
Office B	2Q	0.68	0.25
Office B	3Q	0.78	1.14
Office B	4Q	1.28	2.06
Office B	5Q	1.99	3.79
Office B	6Q	1.01	2.22
Office C	1Q	0	0.1
Office C	2Q	0	0.15
Office C	3Q	0	0.21
Office C	4Q	0	0.37
Office C	5Q	0	0.79
Office C	6Q	0	0.37
Office D	1Q	0	0.39
Office D	2Q	0	1.03
Office D	3Q	0	1.15
Office D	4Q	0	1.35
Office D	5Q	0	1.76
Office D	6Q	0	1.14

Now I need to dervied 2 columns for the Min and Max values. The values would be for the overall data and not for specific office ? Also I have another data column that needs to be shown in the final output

Office	Qtr	Min	Max	Quantity
Office A	1Q	0	0	0.71
Office A	2Q	0.25	0.24	0
Office A	3Q	0.44	0.52	4.78
Office A	4Q	0.85	1.01	0.13
Office A	5Q	1.74	1.09	4.55
Office A	6Q	2.15	1.47	0.09
Office B	1Q	0.41	0.08	0
Office B	2Q	0.68	0.25	0.84
Office B	3Q	0.78	1.14	0
Office B	4Q	1.28	2.06	0
Office B	5Q	1.99	3.79	0
Office B	6Q	1.01	2.22	12.88
Office C	1Q	0	0.1	0.51
Office C	2Q	0	0.15	5.25
Office C	3Q	0	0.21	7.12
Office C	4Q	0	0.37	0.09
Office C	5Q	0	0.79	0
Office C	6Q	0	0.37	1.08
Office D	1Q	0	0.39	0.42
Office D	2Q	0	1.03	0.07
Office D	3Q	0	1.15	0
Office D	4Q	0	1.35	5.59
Office D	5Q	0	1.76	0.7
Office D	6Q	0	1.14	0

Howcan I achieve this ? Since right now on using summarize I see the values are same for every row. Is the approach to be more like a level of detail.

Please advise

Thanks

[atcodedog05]

Hi @JDong 

I think you are using Grouby then Min and Max. Dont use groupby.

And if you can provide expected output and exact logic we can build a workflow accordingly.

[Emil_Kos]

Hi @JDong,

Please find the workflow attached.

And the output:

[atcodedog05]

Hi @JDong 

You might have to do something like this. Remove grouby

Workflow:

Hope this helps 🙂 If it doesnt solve please provide more clarity so we can help better.

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[JDong]

Thanks. Is there a way to consider the Min value by not considering 0 ?

[Emil_Kos]

Hi @JDong,

yes, there is 😀

and the output:

Please mark this as a solution if this was helpful for you!

[atcodedog05]

Hi @JDong 

You can filter 0's before summarizing. There no other straight forward way.

Workflow:

Then for each column you need to handle separately 

Hope this helps 🙂

[grazitti_sapna]

Hi @JDong , give this a try.

Thanks.

[atcodedog05]

Hi @JDong 

Another Neat trick would be to convert all 0's to Null()

Nulls are not considered in aggregates.

And by using Multi-Field formula tool you can replace 0's to Null over multiple column. Later on this reference Null can be changed back to 0's too.

Output:

Hope this helps 🙂

Hi @grazitti_sapna using Field1!=0 and Field2!=0 will cause data issue. If in a row Field1 is 0 and Field2 is overall max. Upon filter this data will be lost.

[JDong]

How can I add the quantity column this along with the Min Max columns since when I add this as group by in summerize tool the values of Min and Max becomes same for all entries.

So this will be a new field apart from Min and Max fields and called as Quantity