VERTICAL PIVOT V8

[mattias.klint]

Hello!

Here is my problem.
Only one column. Only one record on each row.

Heading
A
B
C
1
2
3
Z
X
Y
4
5
6

I need to get this into rows:
A B C 1 2 3
Z X Y 4 5 6

My example data is very accurate. This is the exact way my data is structured.

There are many solutions to this but they all have a key column or a column to group on. I dont

I think that was all,
Mattias

[Teej]

6 rows to 6 columns, always?

One solution - Using Transformer, populate 6 stage variables with each row using a counter. Output the records, and do a remove duplicate for the last record using a row counter (only update every 6 rows.)

One word of caution -- partitioning would be a problem for your solution, run it in sequential mode to guarantee that this will work.

I already came up with 3 other solutions. I leave it to others if they want to share.

[mattias.klint]

Thanks!

Is it possible to do it without one stage variable for each column? I might need more columns in the future. It's always good to be prepared

If you have the time make the description a little more detailed I would be very greatful.. It's been some time since I developed in DS. Please.

Thanks alot,

Mattias

[chulett]

Another thought - build yourself a key / grouping column. A simple stage variable, maybe two, to control the numbering starting from one and only incrementing every six records. If you know you might have more columns coming in the future, is there some way to programmatically know when the increment should happed, rather than hard-coding the logic at six? Or a job parameter would work here.

The end result, which you already seem prepared to handle, would look like this:

Heading
1 A
1 B
1 C
1 1
1 2
1 3
2 Z
2 X
2 Y
2 4
2 5
2 6

[mattias.klint]

Creating a grouping column would be perfect, just the way you describe. You also point out that it's easy with stage variables. Sorry but it doesnt seem to be easy for me. Can you please give me a s short description. It would be very helpful.

Thx,
Mattias

[chulett]

Set the Initial Value of your stage variable to 0 when you define it. The deviation of 'svGroupKey' could be simply:

Code: Select all

If svGroupKey = 7 Then 1 Else svGroupKey + 1

Or if a job parameter controls the group size:

Code: Select all

If svGroupKey = (#GROUP_SIZE# + 1) Then 1 Else svGroupKey + 1

The 'group size + 1' amount could be in a second stage variable (svGroupKeyMax?) where the calculation is done in the Initial Value so it is only done once, not once every row. Leave its derivation blank so it stays a static value.

[mattias.klint]

I tried your first suggestion and it gives me a sequence of numbers, 1 to 6.I would like to get the first 6 rows to be one group, and the following 6 to be another group. This way I will be able to aggregate my rows that look like this with the desired group column included:

"1","Group 1","1","","","",""
"2","Group 1","","2","","",""
"3","Group 1","","","3","",""
"4","Group 1","","","","4",""
"5","Group 1","","","","","5"

It should be possible with stage variables, right?

[chulett]

D'oh! You're right, horked that one up.

Use another stage variable to control the increments of the first. The order is important as they resolve from top to bottom.

svGroupKeyCheck: Initial Value 0
If svGroupKeyCheck = 6 Then 1 Else svGroupKeyCheck + 1

svGroupKey: Initial Value 0
If svGroupKeyCheck = 1 Then svGroupKey + 1 Else svGroupKey

Something like that, anyway, it's early. Play with it!

Or a Mod(@INROWNUM,6) = 0 check could be used rather than the second stage variable.

[mattias.klint]

Works perfect, thank you very much. THX!

Now I will hit the aggregator...