Returning specific values in the field when joining two tables

Question & Answer
, , , , ,
1

Hi everyone! I have Table A and Table B that match id’s with a one-to-many relationship (left join)
I’ve joined the tables. How do I get it to return different values on Table B for different columns (e.g., average, max, min, first, last). For reference, the data is stored in Amazon S3 (OpenSearch) and we’ve converted the data to SPICE to get better performance.

order_id customer_name order_date
1 John Doe 2022-01-01
2 Jane Smith 2022-01-02
3 Bob Johnson 2022-01-03
detail_id order_id product_name
------------ ---------- --------------
101 1 Product A
102 1 Product B
103 2 Product C
104 3 Product A
105 3 Product B
Outcome from Join
order_id customer_name order_date
---------- --------------- -------------
1 John Doe 2022-01-01
2 Jane Smith 2022-01-02
3 Bob Johnson 2022-01-03
2

Hi @rohit_SB
first of all, shouldnt have you outcome 5 rows?
And am I right, there is somehow a “value” column?
BR

3

I’d like to apologise, perhaps my question wasn’t correct:
Here’s the table I’d like to get:
Outcome from Join

order_id customer_name order_date avg_quantity max_price min_price first_product last_product
1 John Doe 2022-01-01 3.5 15 10 Product A Product B
2 Jane Smith 2022-01-02 3 20 20 Product C Product C
3 Bob Johnson 2022-01-03 2.5 15 10 Product A Product B
4

Hello @rohit_SB and @ErikG !

@rohit_SB do you still need help with this issue or were you able to find a solution? If you were able to find a solution could you share it with the community?

5

I have not been able to find a solution yet @duncan, however from asking a few people in other forums, QS does not offer the option to make custom queries if the dataset is stored in S3 and is converted to SPICE

1 Like
6

Hi @rohit_SB
any chance to use Athena?
BR

7

Hi @ErikG Sadly, no. That’ll be an extra cost which is unlikely at the moment. I’ve instead taken a different approach and will try to learn to query from the backend, which is DocDB in our case.

8

Hello @rohit_SB !

I will mark this as a feature request for the Quicksight team!