3 Replies Latest reply on Mar 14, 2016 1:05 PM by Steve Mayer

# Help duplicating a row across multiple corresponding columns with the same unique identifier

I used the tableau excel add-in to normalize some survey data and for the most part it's working well. However, I have a calculated field based off of an answer column and need to apply the result to all related respondent ID columns. The data is organized as follows:

Respondent ID

1234567Do you have a job offer?1Yes
2345678Do you have a job offer?2No
3456789Do you have a job offer?1Yes
4567890Do you have a job offer?3Pending

"Offer?" is the calculated field with the following calculation:

if [Question] = 'Have you received an offer?' and [Answer] = '1' then 'Yes'

else if [Question] = 'Have you received an offer?' and [Answer] = '2' then 'No'

else if [Question] = 'Have you received an offer?' and [Answer] = '3' then 'Pending'

END END END

Now, I simplified the table for this purpose, and in reality there are about 100 rows per respondent ID and this particular question is only one of them. In order to filter the data in my dashboard on the 'Offer?' column, it appears I need to duplicate the calculated answer across all rows for the corresponding Respondent ID. How do I do that? I'm sure this has been answered somewhere else but I don't even know the vocabulary to structure the question in a search, so I'm asking here. I'm aware I could also filter on the 'Answer' column but I'll have the same problem: that there is not a value for every corresponding Respondent ID. Many thanks in advance!

• ###### 1. Re: Help duplicating a row across multiple corresponding columns with the same unique identifier

You can use an LOD calculation to solve this. The formula:

{ FIXED [Respondent ID] : MAX([Offer?]) }

will return the maximum value of [Offer?] across the entire data source for each Respondent ID. For any record for a particular Respondent ID, this should return how they responded to the Offer? question.

You can use this LOD calculated field as a filter just like any other dimension, so you'll be able to view responses only from respondents who received or didn't receive an offer,

-Steve

• ###### 2. Re: Help duplicating a row across multiple corresponding columns with the same unique identifier

Thanks Steve, but when I input that I get ‘Undefined’ as the result.

• ###### 3. Re: Help duplicating a row across multiple corresponding columns with the same unique identifier

Not sure what you mean by 'Undefined'... first of all, does the LOD formula pass with no syntax errors? If no, you'll have to make sure all of the field names match exactly with yours.

Do you have respondents that didn't answer that question? Those respondents will have a NULL [Offer?], which may explain 'Undefined' - if you have aliased NULL with 'Undefined'.

You should start by building a cross-tab in Tableau with Respondent, Question, Offer? and the new LOD calculated field to verify that you are seeing the right data.

If you are still seeing issues, you'll have to post a packaged workbook - maybe with a small sample of your data.

-Steve