Select row keys where another column is unique

PeakPerformance · 09-16-2019 09:41 AM

Hello! I am struggling trying to get a list of data that I need. I have a list of line items with order #, SKU and QTY. Because of how the data entry happens, there are often multiple line items with the same SKU and various QTYs, I would like to create a list of UNIQUE skus by order number (using a viritual column to generate a Total QTY column). Ideally my code would look something like this:

SELECT(ITEMS[Key],AND([Order #]=[_THISROW].[Order #],ISUNIQUE([SKU])))

Obvioulsy the ISUNIQUE function does not exist, but hopefully you get the intention of what I am trying to do.

I tried using… SELECT(ITEMS[SKU],[Order #]=[_THISROW].[Order #],TRUE) and I got a unique list of the SKUs which is great, except I cannot reference back to their rows to get the Total QTY because the SKU column is not the key to the table.

Basically I want to take this…

Key Order # SKU QTY Total QTY
34523 001 A001 2 6
39823 001 A001 2 6
42565 001 A002 4 4
09565 001 A003 1 2
76565 001 A003 1 2

Filtered to this… (doesn’t matter which row key is chosen for the SKU since the total QTY is calculated in all of them)

Key Order # SKU Total QTY
34523 001 A001 6
42565 001 A002 4
76565 001 A003 2

Help!

Steve

Why are you doing this? There’s no efficient way to do what you describe. It would help to understand what you intend to do with the filtered data. Is this for a report? A row filter for a slice? A column? If a column, in which table?

PeakPerformance

Hey Steve,
I am generating a packing list and I want to collapse the QTYs of the same SKUs to make fore easier packing (rather than listing the same SKU over and over with various quantities). The reason there are multiple line items with the same SKU is that they are added as bundle products to a Parent product. For instance, a customer will order various components and click “Add Bolts” which goes out and finds the required hardware and adds the appropriate line items to the order, which often creates this situation.

I developed a work-around where I did most of my computation on a separate google sheet and then just reference the data from within appsheet. So unless there is a much more elegant solution within appsheet, I should be good to go!

siriuslea

I also have the same issue but I would like to avoid using the spreadsheet for this. I need to create a copy of only the group of unique rows

PeakPerformance

Cynthia,
While I haven’t tested it, I considered another option that would not involve a separate spreadsheet. Here is the scenario

LINE ITEMS:

ID SKU. QTY. Order #

```
  1001        10       999999
```
```
  1001        15      999999
```
```
  1001         5       999999
```
```
  1002        10      999999
```
```
  1002        20      999999
```
```
  1002        10      999999
```

You want a condensed view of Order Data that would look like this:
SKU. QTY.
1001 30
1002 40

On your table, you could add a virtual column called Total QTY

SUM(SELECT(LINE ITEMS[QTY],AND([SKU]=[_THISROW].[SKU),[Order #]=[_THISROW].[Order #]))

 SKU        QTY        Order #     Total QTY

  1001        10       999999               30

  1001        15      999999               30

  1001         5       999999               30

  1002        10      999999               40

  1002        20      999999               40

  1002        10      999999               40

You could then create a ref virtual column in your ORDERS table with the following formula

SELECT(LINE ITEMS[SKU],[Order #]=[_THISROW].[Order #],TRUE)

You could then format the display of that data to only show the Total QTY row and not the individual row QTY:

SKU. Total QTY
1001. 30
1002. 40

There may be a more elegant way of doing this, but it is worth a try!