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

[JDong]

Hi @Emil_Kos @atcodedog05 @grazitti_sapna 

I worked on cleaning up the original data and have a sample flow

The expected output is 

Level 1

Level 2

Assetname

Qty Number

Name

Min Value

Max Value

Hope this helps !

Thanks

[Emil_Kos]

Hi @JDong,

Can you also attach an excel file? 

[JDong]

Done

[Emil_Kos · Accepted solution]

Hi @JDong,

Please find attached a workflow I have created for you.

I have removed the positions that I was thinking might be unnecessary for your data output.

Please let me know if this is something you wanted to achieve? 

[JDong]

Thanks I am unable to open file since it is created using a newer version I have 2020.3

[Emil_Kos]

Hi @JDong,

I used a note pad to downgrade it for you:

I changed 2020.4 in the second line to 2020.3.

Just letting you know in case you will need this functionality in the future. 

[JDong]

Thanks , the problem however is when we include QtyNumber column in the output.

Can you please advise.

[Emil_Kos]

Hi @JDong,

Qnty number is a column from different data source? 

[grazitti_sapna]

Hi @JDong . you can try this.

I removed Qty Number from the summarize tool and that is how it is working. 

Is this what you are trying to achieve?

Thanks.

[JDong]

I need the Qty number field in the final output and it is in same source like the excel I shared.

Thanks