8 Replies Latest reply on Aug 23, 2018 10:51 PM by Ankit Agarwal

# Calculate unique values for same row values

Hi,

I mostly find solutions by browsing through the community forum. However I could not find a solution to this problem. Please help as I need this desperately.

Here is what I have

 Order No Order Line No Booking Desired Result 1 1.1 5000 5000 1 1.1 5000 NULL 1 1.1 2000 2000 1 1.2 3000 3000 1 1.2 3000 NULL 1 1.3 12000 12000 2 2.1 150 150 2 2.1 150 NULL 2 2.1 250 250 3 3.1 400 400 3 3.1 500 500

I do not want to blend my data nor create an aggregated filed. I want to create a calculated field which will generate the 'Desired Result' column, which I can use for my other analysis.

• ###### 1. Re: Calculate unique values for same row values

Tableau will collapse those duplicate records into a single row. Do you have any ID in your dataset uniquely identifying each row?

This could be a Row ID, Order ID, Order Date etc.

• ###### 2. Re: Calculate unique values for same row values

No I do not. I TRIED to concatenate order line number and Booking value (1.1_5000, 1.1_2000 etc) and compare 1 concatenated row with another.

Desired Logic,

If concatenated_field = concatenated_field then

keep only 1st booking value and make other NULL.

END

But I could not do this calculation

• ###### 3. Re: Calculate unique values for same row values

I have added a Row ID as a unique identifier. You can right click on the field name and uncheck 'Show Header' to hide the column. See attached workbook.

Hope this helps.

Ossai

• ###### 4. Re: Calculate unique values for same row values

Hi Okechukwu,

Thank you so much for the calculated formula.. But..

My data-source does not have any unique Row_ID.  Can I generate one? (Perhaps using Index()?)

I'm not sure if the index() will also work in the background or only when used on the sheet.

I wanted to consult before applying the formula.

Thanks

Ankit

• ###### 5. Re: Calculate unique values for same row values

Index() is a table calculation and therefore cannot be mixed with LOD.

You can still use Index and Lookup to get the correct result. However, bear in mind that you are limited in what you can do with the results. For example, you cannot just pluck the result and use it in every chart or calculation, without having all the fields used in generating the calculation in the view. See attached workbook.

Step 1: Go to Analysis and uncheck 'Aggregate Measures'.

Step 2:

• ###### 6. Re: Calculate unique values for same row values

Hey Oke,

The second solution worked like a charm. Thank you so much. Now I can use this calculated field to create my other calculations such as discount and other stuff...

BIG Relief. BIG Thanking!

Ankit

• ###### 7. Re: Calculate unique values for same row values

You're welcome.