VERTICAL PIVOT V8
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 43
- Joined: Wed Oct 18, 2006 6:03 am
VERTICAL PIVOT V8
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
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
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.
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.
-
- Premium Member
- Posts: 43
- Joined: Wed Oct 18, 2006 6:03 am
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
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
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
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
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 43
- Joined: Wed Oct 18, 2006 6:03 am
Set the Initial Value of your stage variable to 0 when you define it. The deviation of 'svGroupKey' could be simply:
Or if a job parameter controls the group size:
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.
Code: Select all
If svGroupKey = 7 Then 1 Else svGroupKey + 1
Code: Select all
If svGroupKey = (#GROUP_SIZE# + 1) Then 1 Else svGroupKey + 1
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 43
- Joined: Wed Oct 18, 2006 6:03 am
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?
"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?
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 43
- Joined: Wed Oct 18, 2006 6:03 am