Hi Alterx Community: I need help converting this table in a single row record: RegionEast Vehicle TypeSedan MakeHonda StateNJ Dealer IdDealership NameYear in BusinessEffective Year 1001Auto Hub52015Meet Desired Output: Dealer IdDealership NameYear in BusinessEffective YearSales TargetRegionVehicle TypeMakeState1001Auto Hub52015MeetEastSedanHondaNJ Thanks, Rana

Combine Coloumn Data with related Rows

Hi Alterx Community:

I need help converting this table in a single row record:

			Region	East
			Vehicle Type	Sedan
			Make	Honda
			State	NJ

Dealer Id	Dealership Name	Year in Business	Effective Year
1001	Auto Hub	5	2015	Meet

Desired Output:

Dealer Id	Dealership Name	Year in Business	Effective Year	Sales Target	Region	Vehicle Type	Make	State
1001	Auto Hub	5	2015	Meet	East	Sedan	Honda	NJ

Thanks,

Rana

Transformation

Accepted answers

afv2688

Hello @itahir04,

Would this help?

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

join_data.yxmd

All comments

fmvizcaino

Hi @itahir04 ,

Are the tables presented in your example 2 tables or only one in the same sheet?

Best,

Fernando Vizcaino

itahir04

Hi Fernando,

Example sheet has the same sample data as posted.

FIrst table is the sample data.

Second table is the desired outcome.

Thanks for your assistance.

Rana

itahir04

Hi Fernando,

I have simplified my data set. Hopefully that will help.

Thanks,

Rana

afv2688

Hello @itahir04,

Would this help?

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

join_data.yxmd

itahir04

Hi afv2688,

Thanks for providng this simple solution, it provides the end result I was looking for.

Kind Regards,

Rana

itahir04

Hi afv2688,

I have implemented a workflow following this logic and modify a little bit to meet my need. There is a little challenge as I apply this to large set of data.

For exampe if there are more dealers and Vehicles.

Right now, I am creating one data set. Then create second, then join both. Please see attached workflow. But this way I have to use so many blend and join tool.

I am wondering how can I create this with more efficiently. If you can please review my workflow and give me some idea how can I use same approach with large set of data, that would be great.

			Region	East	West	South
			Vehicle Type	Sedan	Sedan	SUV
			Make	Honda	Toyota	Suburu
			State	NJ	PA	UT

Dealer Id	Dealership Name	Year in Business	Effective Year
1001	Auto Hub	5	2015	Not Sure	Not Meet	Meet
1002	Auto Heaven	10	2010	Not Sure	Not Meet	Meet
1003	Auto Bash	20	2000	Not Sure	Not Meet	Meet
1004	Sanson Toyota	30	1990	Not Sure	Not Meet	Meet

STS-joindata.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703