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

[grazitti_sapna]

Hi @atcodedog05 , thanks for pointing out I did not consider the case you described, but have modified my workflow, try your test case in this workflow. Also your way to replace the zeroes to null is a smart move.

@JDong - This is an alternate method to approach in case you have dynamic columns.

Thanks.

[grazitti_sapna]

@JDong , you are right if you try to join the median column with min max it will definitely give you same values for min max and different values for median. Is there a way to find on what basis are you calculating this median?

Thanks.

[atcodedog05]

Hi @JDong 

Bit confused need more clarity is this what you are trying to achieve.

It has medina which considers 0's and median without 0's as well

Workflow:

Hope this helps 🙂

[JDong]

Thanks.

Quantiy is just another Data column (Stored as string) and not that I wish to summerize.

I just want to display the column in the final result.

[atcodedog05]

Hi @JDong 

Can you check the above solution once.

And If its ok can you provide your actually like 10 rows would and your workflow. We can help you much effectively 🙂

[JDong]

@atcodedog05 @grazitti_sapna @Emil_Kos 

I have updated all my posts so it is more clear to read and understand. Do let me know if there are any questions.

Current output is as shown above where again the grouping is happening. The last field is grouped by in summerize step but I just want to display the value.

Thanks

[atcodedog05]

Hi @JDong 

If you are using groupby in summarize it applies on all irrespective of order. Hence you need 2 summarize tool one for aggregates with groupby and another for without groupby. Later you can use join tool or append tool.

It is not possible to configure a summarize tool to take aggregation on groupby and some not.

In the above solution you can use groupby in top summarize tool which is aggregating median.

I currently away from system. I will work on workflow once back. Please provide the expected output so that we can target on achieving it.

[Emil_Kos]

Hi @JDong,

It is also unclear for me what is the expected result. 

If you could give us some more details that would be awesome!

[JDong]

 @Emil_Kos 

Whatever @atcodedog05  understands is the same result I wish to achieve. As in the image above I am now getting the Max , Min values after removing the quantity field from group by. I also added another summerize for the quantity field but if I join based on 3 keys I get too many records. Append also similar.

How can I fix this to get correct result.

Please note that when I do a join on record position the records seems right..but due to null values in dataset the values are not exactly on the same rows.

Thanks

[Emil_Kos]

Hi @JDong,

can you attach your workflow?