Cross-tab or Transpose of blocks of columns (load data into excel)

[timewaste]

Hi Peers,

I am trying to transpose the data into 3 sets to write back in to excel. Please see below for my source data:

V	ED	D	F	SF	SPH	T	H
R	B	G	NC	Te	PH	M	14.4
R	B	G	NC	Te	PH	A	1.6
R	B	G	NC	Te	PH	V	0
R	B	A	NC	Bu	PH	M	16
R	B	A	NC	Bu	PH	A	0
R	B	A	NC	Bu	PH	V	0
R	B	A	NC	Zi	PH	M	0
R	B	A	NC	Zi	PH	A	0
R	B	A	NC	Zi	PH	V	0

and my output must look like:

	V	R	R	R	R	R	R	R	R	R
	ED	B	B	B	B	B	B	B	B	B
	D	G	G	G	A	A	A	A	A	A
	F	NC	NC	NC	NC	NC	NC	NC	NC	NC
	SF	Te	Te	Te	Bu	Bu	Bu	Zi	Zi	Zi
	T	M	A	V	M	A	V	M	A	V
SPH	PH	14.4	1.6	0	16	0	0	0	0	0

How can I achieve this? Please advise.

TIA!

[garthheward]

I think you can get most of the way to your result with the TotallyTranspose macro.

After running that on your data, it's just a shift of your PH/SPH names

[danilang · Accepted solution]

hi @timewaste

Like @garthheward mentioned, the Totally Transpose macro will get you close, but you need to add a record ID column for the macro to use as output column names.  Also, the macro row output is alphabetical by the 1st column,  like so

D 
ED 
F 
H 
SF 
SPH 

T 

V 

You want them in original column order starting with the V row.  In order to easily do this, you'll need to a column order column after the transpose inside the Macro.  Since debugging macros is tricky, I've attached a standard workflow that gives the output you want.

Dan

[timewaste]

@danilang Wow! U made it look so simple. Thanks a ton! @garthheward Sure, will check it out.

[timewaste]

@danilang So in my real case, I have total of 266544 records as input. When I am processing using above workflow, I have noticed that at cross-tab stage, it would need to generate more than 1000 fields(columns), hence throwing an exception:

Warning: Cross Tab (19): More than 1000 fields are being generated by the CrossTab

Is there an alternative approach to this at that step? Or can I reset the configuration somewhere to accommodate all the fields. In my data set, I would have total of 33318 fields after cross-tab stage in output (just fyi based on my data). 

Please advise.

TIA!

[danilang]

Hi @timewaste

With 266544 records as input, you should get that many columns as output after the cross tab unless you get duplicates, in which case, the values will be concatenated. 

You're also running up against an excel limit, here.  You can only have 16,384 columns in an excel sheet.  

Maybe you could explain what your overall goal is.  Why do you need to crosstab?  Is there a reason you can't work with the data in row order as opposed to column order?  That many columns in excel can't be for human consumption.  Do you have a pivot table or graph output that requires the data in that form?

Dan 

[timewaste]

@danilang Yes, my bad, I filtered the data to not include zeros and could bring down the data set to ~21000 rows and could perform crosstab. So, here's my situation. My data has multiple possibilities to grow. It was my bad that I gave a sample which was one dimensional above. Here's how the data would actually look like:

V	ED	D	F	SF	SPH	T	H
R	B	G	NC	Te	PH	M	14.4
R	B	G	NC	Te	PH	A	1.6
R	B	G	NC	Te	PH	V	0
R	B	G	NC	Te	AP1	M	2.5
R	B	G	NC	Te	AP1	A	6
R	B	G	NC	Te	AP1	V	0
R	B	A	NC	Bu	HP1	M	16
R	B	A	NC	Bu	HP1	A	0
R	B	A	NC	Bu	HP1	V	0
R	B	A	NC	Zi	PH2	M	0
R	B	A	NC	Zi	PH2	A	8
R	B	A	NC	Zi	PH2	V	0

So, the values in SPH column can vary and come in as input data in multiple combinations as shown above (in colors pink/black and red denotes change in values in SPH column). So, from your solution... at the formula tool stage (in adding PreName, Name and ColID), how can I make sure that it dynamically picks the value in SPH and assigns it to Name filed in the output as shown below?

	V	R	R	R	R	R	R	R	R	R
	ED	B	B	B	B	B	B	B	B	B
	D	G	G	G	A	A	A	A	A	A
	F	NC	NC	NC	NC	NC	NC	NC	NC	NC
	SF	Te	Te	Te	Bu	Bu	Bu	Zi	Zi	Zi
	T	M	A	V	M	A	V	M	A	V
SPH	PH	14.4	1.6	0	0	0	0	0	0	0
SPH	AP1	2.5	6	0	0	0	0	0	0	0
SPH	HP1	0	0	0	16	0	0	0	0	0
SPH	PH2	0	0	0	0	0	0	0	8	0

Please advise. Thanks a ton!

[timewaste]

@danilang I am not sure if you received my previous response but I dont seem to find it on the thread here. Anyways, here's the actual question:

Yes, I was able to figure out the cross tab step. I removed all values that are equal to 0 and was able to limit the rows that I need to work on but, the challenge I face now is to get to the right output as shown below for multiple combinations of SPH in input data.

Input Data:

V	ED	D	F	SF	SPH	T	H
R	B	G	NC	Te	PH	M	14.4
R	B	G	NC	Te	PH	A	1.6
R	B	G	NC	Te	PH	V	0
R	B	G	NC	Te	OP1	M	14.4
R	B	G	NC	Te	OP1	A	1.6
R	B	G	NC	Te	OP1	V	0
R	B	G	NC	Te1	PH	M	2.5
R	B	G	NC	Te1	PH	A	6
R	B	G	NC	Te1	PH	V	0
R	B	G	NC	Te1	AP1	M	2.5
R	B	G	NC	Te1	AP1	A	6
R	B	G	NC	Te1	AP1	V	0
R	B	A	NC	Bu	HP1	M	16
R	B	A	NC	Bu	HP1	A	0
R	B	A	NC	Bu	HP1	V	0
R	B	A	NC	Zi	PH2	M	8
R	B	A	NC	Zi	PH2	A	8
R	B	A	NC	Zi	PH2	V	0

And the output data must look like:

PreName	Name	1	2	3	4	5	6	7	8	9	10	11	12
	V	R	R	R	R	R	R	R	R	R	R	R	R
	ED	B	B	B	B	B	B	B	B	B	B	B	B
	D	G	G	G	G	G	G	A	A	A	A	A	A
	F	NC	NC	NC	NC	NC	NC	NC	NC	NC	NC	NC	NC
	SF	Te	Te	Te	Te1	Te1	Te1	Bu	Bu	Bu	Zi	Zi	Zi
	T	M	A	V	M	A	V	M	A	V	M	A	V
SPH	PH	14.4	1.6	0	0	0	0	0	0	0	0	0	0
SPH	OP1	14.4	1.6	0	0	0	0	0	0	0	0	0	0
SPH	PH	0	0	0	2.5	6	0	0	0	0	0	0	0
SPH	AP1	0	0	0	2.5	6	0	0	0	0	0	0	0
SPH	HP1	0	0	0	0	0	0	16	0	0	0	0	0
SPH	PH2	0	0	0	0	0	0	0	0	0	8	8	0

and so on... Please advise. Thanks a ton!

[danilang]

Any chance you could recreate the missing message. It's got some details that sound important.  I've just got the text in my inbox with no images. Secifically this part

", the values in SPH column can vary and come in as input data in multiple combinations as shown above in colors blue and black and red denotes change in values in SPH column"

So your output will look like this

PreName	Name	1	2	3	4	5	6	7	8	9	10	11	12
	V	R	R	R	R	R	R	R	R	R	R	R	R
	ED	B	B	B	B	B	B	B	B	B	B	B	B
	D	G	G	G	G	G	G	A	A	A	A	A	A
	F	NC	NC	NC	NC	NC	NC	NC	NC	NC	NC	NC	NC
	SF	Te	Te	Te	Te1	Te1	Te1	Bu	Bu	Bu	Zi	Zi	Zi
	T	M	A	V	M	A	V	M	A	V	M	A	V
SPH	PH	14.4	1.6	0	0	0	0	0	0	0	0	0	0
SPH	OP1	14.4	1.6	0	0	0	0	0	0	0	0	0	0
SPH	PH	0	0	0	2.5	6	0	0	0	0	0	0	0
SPH	AP1	0	0	0	2.5	6	0	0	0	0	0	0	0
SPH	HP1	0	0	0	0	0	0	16	0	0	0	0	0
SPH	PH2	0	0	0	0	0	0	0	0	0	8	8	0

but with 30,000 columns?  Or have gotten the output down to a reasonable number of columns?

Dan

[timewaste]

@danilang Well, you can ignore the entire message or even missing piece of the message. Essentially, what I wrote in that message was you can ignore 30,000 columns issue I face. I was able to cut down the data to few thousands of columns (5312 columns) by using a filter tool. So, I am good there.

The problem I am facing is at the formula tool step - populating PreName and Name columns. I mean if my source data doesn't have just "PH", rather has multiple values like "PH", "PH1", "OP1", "AP1" so on in SPH column for the same combination (V --> ED --> D --> F --> SF)... I have highlighted one use case in Orange and all different values in Red... what do I do? How do I populate output data shown below based on below input data?

Input Data:

V	ED	D	F	SF	SPH	T	H
R	B	G	NC	Te	PH	M	14.4
R	B	G	NC	Te	PH	A	1.6
R	B	G	NC	Te	PH	V	0
R	B	G	NC	Te	OP1	M	14.4
R	B	G	NC	Te	OP1	A	1.6
R	B	G	NC	Te	OP1	V	0
R	B	G	NC	Te1	PH	M	2.5
R	B	G	NC	Te1	PH	A	6
R	B	G	NC	Te1	PH	V	0
R	B	G	NC	Te1	AP1	M	2.5
R	B	G	NC	Te1	AP1	A	6
R	B	G	NC	Te1	AP1	V	0
R	B	A	NC	Bu	HP1	M	16
R	B	A	NC	Bu	HP1	A	0
R	B	A	NC	Bu	HP1	V	0
R	B	A	NC	Zi	PH2	M	8
R	B	A	NC	Zi	PH2	A	8
R	B	A	NC	Zi	PH2	V	0

And the output data must look like:

PreName	Name	1	2	3	4	5	6	7	8	9	10	11	12
	V	R	R	R	R	R	R	R	R	R	R	R	R
	ED	B	B	B	B	B	B	B	B	B	B	B	B
	D	G	G	G	G	G	G	A	A	A	A	A	A
	F	NC	NC	NC	NC	NC	NC	NC	NC	NC	NC	NC	NC
	SF	Te	Te	Te	Te1	Te1	Te1	Bu	Bu	Bu	Zi	Zi	Zi
	T	M	A	V	M	A	V	M	A	V	M	A	V
SPH	PH	14.4	1.6	0	0	0	0	0	0	0	0	0	0
SPH	OP1	14.4	1.6	0	0	0	0	0	0	0	0	0	0
SPH	PH	0	0	0	2.5	6	0	0	0	0	0	0	0
SPH	AP1	0	0	0	2.5	6	0	0	0	0	0	0	0
SPH	HP1	0	0	0	0	0	0	16	0	0	0	0	0
SPH	PH2	0	0	0	0	0	0	0	0	0	8	8	0

and so on... Please advise. Thanks a ton!