Combine Coloumn Data with related Rows

[itahir04]

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

[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 · Accepted solution]

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

[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