verical pivot with max input rows unknown

[iwin]

Hi All,
I am trying to do Vertical Pivot as defined in the following link which work when the max no of input rows is known:

viewtopic.php?t=118685

But in my case the max no of input rows for a key is not known. Any suggestions plz.

Thanks.

[sud]

Check out the keychange columns generated in the sort stage. They will help you to identify when you move from one key record group to the next.

[rameshrr3]

I have a solution using server jobs, you can try to convert it to parallel( Shouldn't be a great deal)- Im not

Thanks
Ramesh

[chulett]

So what was the resolution here?

[iwin]

Hi ,
I tried using 4 stage variables and concatenating the values for the same key and later used remove duplicate stage to retain the last one.

Code: Select all

1) keycol                                     -----   SvCurr

2) SvCurr <> SvPrev                             -----  SvCheck

3) If svCheck then lnk.value 
else 
svSvValue :', ': lnk.value                        -----  SvValue


4) SvCurr                                              -----  SvPrev 

[rameshrr3]

I have the generated OSH for a parallel job which works

Code: Select all

#################################################################
#### STAGE: Transformer_17
## Operator
transform
## Operator options
-flag run
-name 'V1S0_TestPivot_Transformer_17'

## General options
[ident('Transformer_17'); jobmon_ident('Transformer_17')]
## Inputs
0< [] 'Lookup_13:DSLink18.v'
## Outputs
0> [] 'Transformer_17:DSLink19.v'
;

#################################################################
#### STAGE: Lookup_13
## Operator
lookup
## Operator options
-table
-key KEY
-ifNotFound fail

## General options
[ident('Lookup_13'); jobmon_ident('Lookup_13')]
## Inputs
0< [] 'Remove_Duplicates_8:DSLink14.v'
1< [] 'Aggregator_7:DSLink11.v'
## Outputs
0> [modify (
keep
  KEY,CODE,COUNT;)] 'Lookup_13:DSLink18.v'
;

#################################################################
#### STAGE: Remove_Duplicates_8
## Operator
remdup
## Operator options
-keep last
-key 'KEY'

## General options
[ident('Remove_Duplicates_8'); jobmon_ident('Remove_Duplicates_8')]
## Inputs
0< [] 'Transformer_2:DSLink9.v'
## Outputs
0> [modify (
keep
  KEY,CODE;
)] 'Remove_Duplicates_8:DSLink14.v'
;

#################################################################
#### STAGE: Aggregator_7
## Operator
group
## Operator options
-hash
-key 'KEY'
-countField 'COUNT'

## General options
[ident('Aggregator_7'); jobmon_ident('Aggregator_7')]
## Inputs
0< [] 'Transformer_2:DSLink6.v'
## Outputs
0> [modify (
  COUNT:nullable int32=COUNT;
keep
  KEY;
)] 'Aggregator_7:DSLink11.v'
;

#################################################################
#### STAGE: Transformer_2
## Operator
transform
## Operator options
-flag run
-name 'V1S5_TestPivot_Transformer_2'

## General options
[ident('Transformer_2'); jobmon_ident('Transformer_2')]
## Inputs
0< [] 'Sort_1:DSLink4.v'
## Outputs
0> [] 'Transformer_2:DSLink6.v'
1> [] 'Transformer_2:DSLink9.v'
;

#################################################################
#### STAGE: Sort_1
## Operator
tsort
## Operator options
-stable
-key 'KEY'
-asc

## General options
[ident('Sort_1'); jobmon_ident('Sort_1')]
## Inputs
0< [] 'Sequential_File_0:DSLink3.v'
## Outputs
0> [modify (
keep
  KEY,CODE;
)] 'Sort_1:DSLink4.v'
;

#################################################################
#### STAGE: Sequential_File_0
## Operator
import
## Operator options
-schema record
  {final_delim=end, delim=ws, quote=double}
(
  KEY:int32;
  CODE:string[max=4];
)
-file  '/home/isadmin/vp.txt'
-rejects continue
-reportProgress yes

## General options
[ident('Sequential_File_0'); jobmon_ident('Sequential_File_0')]
## Outputs
0> [] 'Sequential_File_0:DSLink3.v'
;

#################################################################
#### STAGE: Sequential_File_1
## Operator
export
## Operator options
-schema record
  {final_delim=end, delim=',', quote=double}
(
  KEY:int32;
  CODE_SET:string[max=255];
)
-file '/home/dsadm/vout'
-overwrite
-rejects continue

## General options
[ident('Sequential_File_1'); jobmon_ident('Sequential_File_1')]
## Inputs
0< [] 'Transformer_17:DSLink19.v'
;

[dstage2006]

I tried to understand from this post how to do the Vertical Pivot for rows unknown.

I tried same from this post still i am unable to get the logic for row unknown (viewtopic.php?t=118685)

My requirement is

INPUT COLUMNS
A 1
A 2
A 3
B 2
B 3
B 4
C 1
C 5
C 6

Output:

A 1 2 3 - - -
B - 2 3 4 - -
C 1 - - - 5 6

Thanks in advance.