CODEWITHSUNDEEP
sqlselectduplicatestableau-apiwindow-functions
greeneyesfifa
greeneyesfifa

Reputation: 25

How to avoid repeating row values in field hierarchy when joining over an ID

I am trying to figure out a way to make rows of top level of hierarchy appear only once in SQL, directly or via an extra column:

enter image description here

Quantity 1 is from Table 1, Quantity 2 is from Table 2, and they are joined over the ID field. Q1 and Q2 have nothing to do with one another mathematically speaking. I would like to have the following column as a result, because I will be summing Q1 and visualizing it in parallel with the individual values of Q2.

enter image description here

Is there any way to do this with SQL? Or in Tableau if anyone knows a solution, as I will be plotting the data in Tableau.

Thanks in advance for your help.

Upvotes: 1

Views: 80

Answers (3)

Alex Blakemore
Alex Blakemore

Reputation: 11921

Tableau has a feature called data blending that is a good fit in situations like this. With data blending, you are making parallel independent queries to two different data sources (one of which is distinguished to be the primary data source) Then the (aggregated) query results from each data source are combined on the client side in a way that is similar to a client-side left-join of the aggregated results (where the primary source is on the left)

So if your data rows do not have a meaningful relationship, just that two tables can be summarized grouping by similar fields, then see if data blending will work for your case. See the Tableau help for details. Some of the edge cases in data blending can get a bit arcane, but the basic straightforward cases work out quite well.

Upvotes: 1

Andy TAR Sols
Andy TAR Sols

Reputation: 1735

For Tableau only you could take the MIN or MAX per ID and sum that. Depending on your viz and what you want to do it could be a simple MIN does the job. Maybe you need to sum the results, so could do a 

WINDOW_SUM(MIN([Qty1]))

Another option is a FIXED function:

{FIXED [ID]: MIN([Qty1])}

You can sum that. Performance may be affected with very large data sets.

Upvotes: 1

GMB
GMB

Reputation: 222622

You can use row_number() - but you need a column that defines the order of records (something that is unique across the whole table, or at least across records that have the same id). I assumed that you have such column and that it is called ordering_id:

select 
    t.*,
    case when row_number() over(partition by id order by ordering_id) = 1
        then quantity1
    end quantity1_edit
from mytable

Upvotes: 1

Related Questions