Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Transposing two sources duplicates a value

MaryB
7 - Meteor

Newbie here...

 

I have two data sources.

One excel table that has 3 rows - a row for each region and a value in 12 columns (one for each month). Pretty simple. I've transposed this to have a region column and one date column and a value column.

 

Region A     August     10

Region A     September    7

etc.

 

The second excel sheet has many rows but still simple. There is a column for region, project name, date and a value. Rows are per project.

 

Region A    Project Alpha      300      August

Region A    Project Beta        400      August

 

When I join the two I keep duplicating the value in my first sheet because it is grouping by the project name. Thus it will look like this:

Region A   August        Project Alpha           10 (from sheet 1)    300 (from sheet 2)

Region A   August        Project Beta             10 (from sheet 1)    400 (from sheet 2)

 

Thus, when I want to just look at August and the value from sheet 1, it will show 20 and not 10 as it should.

 

Is there a way to not duplicate this value?

3 REPLIES 3
MarqueeCrew
20 - Arcturus
20 - Arcturus

can you provide a sample module with TEXT INPUTS?

 

I think it would be easier to understand and to help you with a functioning module.

 

Thanks,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
TonyM
Alteryx Alumni (Retired)

Hi Mary,

 

Given the data provided, the only way to not duplicate values will be to add a unique field to each dataset.

 

If you are joining by Region, or Region and Month, each will produce duplicates.

 

"Region A August 10" will match to both "Region A August Project Alpha" AND "Region A August Project Beta", thus producing a duplicate. 

 

Best,

MaryB
7 - Meteor

I was able to resolve this using the Union module instead of the Join module as it didn't create extra rows and it put "nulls" in the right place. Then I transposed my measure columns into one column and it took correctly.

 

Thanks for the help everyone.

Labels