Unique Count for each customer

Question

Hello,

I am trying to design a solution to update the unique count for each customer. I have a large data set in which Qty of the customers duplicates in an inconsistent way which provide inaccurate result in my analysis. Could anyone please assist me in drawing a flow that provides the correct result i.e. 1 Qty for each customer? Please refer to the attached file for the details.

Example 09.18.xlsx

Raj · Answer

hope this helps

New Workflow15.yxmd

Peachyco · Answer

I'm thinking that this can also be achieved by the Tile Tool and the Summarize Tool.

* Tile by the Unique Values under [Customer Number]
* At the same time, Summarize to get the [Max Qty] for each [Customer Number]
* Join the Summarized data to the Tiled data
* Use the Formula tool to use the [Max Qty] for each record where [Tile Sequence Num] = 1, and zero for everything else. [Tile Sequence Num] = 1 refers to the first record for each [Customer Number].

Gawa · Answer

hi @Learner09

I suppose your demand can be realized by simply using Summarize or Sort/Sample, if you want to extract MAXIMUM Qty. of each customer.

Either way of using Summarize or Sort/Sample can be OK in this case. Please refer to 1st container in attached workflow.

Moreover, if you want to have a format like your attached Excel file(maintain original record, and add column with max Qty. at first row, and 0 for the rest for each customer), please refer to 2nd container in attached workflow.

1187573_gawa.yxmd