Page 1 of 1
Column to rows
Posted: Fri Jan 04, 2008 4:38 pm
by turash
I am getting data in sequential file as follows
Input
- IssuingCompanyCd,013550
PolOfficeCd,000130
PolicyNo,WR101
CertificateNo,101010
RenlCertNo,000000
EndorsementEffDt,01012008
EndorsementSqn,1
Want Convert Output Sequential File like the Following
Output
IssuingCompanyCd,PolOfficeCd,PolicyNo,CertificateNo,RenlCertNo,EndorsementEffDt,EndorsementSqn
013550,000130,WR101,101010,000000,01012008,1
First row is as header in this case
I have tried use pivot, Row Merge without much of luck
Thanks In advance,
Tushar
Posted: Fri Jan 04, 2008 4:46 pm
by chulett
You would need to use stage variables to (ultimately) build a concatenated record (including field delimiters) and then only write it out when you have a 'complete record' as one long varchar.
That could be when you read the "EndorsementSqn" record or after you've read 7 records, depending on if you always get all the pieces or not.
Posted: Fri Jan 04, 2008 4:54 pm
by turash
I am trying to make 2 records out of the 7 records
1st Record contains first columns of these 7 rows
2nd Record contains second columns of these 7 rows.
Could you please give me more details?
chulett wrote:You would need to use stage variables to (ultimately) build a concatenated record (including field delimiters) and then only write it out when you have a 'complete record' as one long varchar.
That could be when you read the "EndorsementSqn" record or after you've read 7 records, depending on if you always get all the pieces or not.
Posted: Fri Jan 04, 2008 4:57 pm
by chulett
You are trying to make 2 records out of
each set of 7 records? Or do you only need to produce the 'heading' row once?
![Confused :?](./images/smilies/icon_confused.gif)
Posted: Mon Jan 07, 2008 10:46 am
by turash
chulett wrote:You are trying to make 2 records out of
each set of 7 records? Or do you only need to produce the 'heading' row once?
![Confused :?](./images/smilies/icon_confused.gif)
Lets talk about multi value flat file
Input
IssuingCompanyCd,013550
PolOfficeCd,000130
PolicyNo,WR101
CertificateNo,101010
RenlCertNo,000000
EndorsementEffDt,01012008
EndorsementSqn,1
IssuingCompanyCd,013551
PolOfficeCd,000131
PolicyNo,WR102
CertificateNo,101012
RenlCertNo,000002
EndorsementEffDt,01012009
EndorsementSqn,2
Output
IssuingCompanyCd,PolOfficeCd,PolicyNo,CertificateNo,RenlCertNo,EndorsementEffDt,EndorsementSqn
013550,000130,WR101,101010,000000,01012008,1
013551,000131,WR102,101012,000002,01012009,2
I have to create the header only once and create two more rows with its values.
Posted: Mon Jan 07, 2008 11:42 am
by ray.wurlod
Code: Select all
If @OUTROWNUM = 1 Then InLink.Field001 Else InLink.Field002
Preserve these values in stage variabales. Constrain your output such that
Code: Select all
InLink.Field001 = 'EndorsementSqn'
Posted: Mon Jan 07, 2008 12:34 pm
by chulett
If these header names are known in advance, just name your output columns appropriately and ignore them in the data.
Posted: Tue Jan 08, 2008 8:58 am
by sachin1
Hello,
In hurry i tried working for your doubt.
job design like
Input file ----->Transformer------>(2 output)
you will require two stage variables.
one colvalue and other valuecolumns.(stage variables).
your two input columns will be colname and colvalue.
define for colvalue -->colvalue :',': DSLink11.colname.
define for valuecolumns-->If mod(@INROWNUM,7)=0 Then ' ' Else trim(valuecolumns):',': DSLink11.colvalueinp.
in first output put the value for target column as trim(colvalue,',') and in constraint put (@INROWNUM=7),because you have 7 columns.
in second output put value for target column as trim(valuecolumns,',','l') and in constraint put (mod(@INROWNUM,7)=6).
file file in output should be used in second output in append mode.
hope that works for you.
thanks.
Posted: Tue Jan 08, 2008 8:59 am
by sachin1
your two input columns will be colname and colvalueinp.