how to load multiple header ,detail and footer records into a file

[Mkrishna]

hi, iam having a data in a file with multi schema like header and detail&footer now

1.how can i load this data into a single file

see my input (Positional file)

HA00000000051070UPAULINA             Wdavid                 john

DA00000000051070S000009003RESSupply    01012015Summer Charge   240 kwh 200 

DA00000000051070S000009003RESSupply    01012015Summer Charge   240 kwh 220

FA00000000051070S000009003M000004576ELECT1 010120150131201531

HA00000000155650UPAULINA             Wraj                 warner

DA00000000155650S000009003RESSupply    01012015Summer Charge   140 kwh 110 

DA00000000155650000009003RESSupply    01012015Summer Charge   340 kwh 300

FA00000000155650S000009003M000004576ELECT1 010120150131201531

like data is for more than one customer with multiple header,detail&footer 

[Mkrishna]

i have to load a file with header detail and footer record in to a single file but when we load the records separate sequence number should be created for header and detail record, how can we achieve this.

example source

BIA00000000051070UPAULINA
SVA00000000051070S000009003RESFee
SVA00000000051070S000009003RESSupply
SVA00000000051070S000009003RESSupply
MTA00000000051070S000009003M000004576EL
BIA00000000091687UJACKSON
SVA00000000091687S000001365RESFee
SVA00000000091687S000001365RESSupply
SVA00000000091687S000001365RESSupply
MTA00000000091687S000001365M000001772E

Here BI is header and sva is detail mat is footer records

Req: i need to generate two sequence no as  below as

 seaqno

    1   BIA00000000051070UPAULINA
    1 1SVA00000000051070S000009003RESFee
    1 2 SVA00000000051070S000009003RESSupply
    1 3 SVA00000000051070S000009003RESSupply
    1 4 MTA00000000051070S000009003M000004576EL
    2    BIA00000000091687UJACKSON
    2 1 SVA00000000091687S000001365RESFee
    2 2 SVA00000000091687S000001365RESSupply
    2 3 SVA00000000091687S000001365RESSupply
    2 4 MTA00000000091687S000001365M000001772E

[aranax]

Define the schema as  as 1,* in tFileinputPositional as from the data given it appears that 1st character signifies which type of row we are currently on using this you will avoid the incorrect spacing issue that arises when you append the file from multiple sources , hence you will have only 2 columns 1st being the rowtype character, rest being the rest of row if there are any values that need to be modified use substring() method to pick out the value from the file.

[Sid4U]

which is the header,footer and detail in this example?

what do you mean by multi schema here, you have 3 data sets in the source file?

Please explain in detail.

[Mkrishna]

Hi aranax thank you for answering,

 can you please explain me how to achieve this.......................... becoz here Header fields (H)rows are different lenght and Detail (D) fields are different length and footer fields are different length.

[aranax]

TO start off, the tFileInputPositional pattern should be 1,*  and set the the length of the column as the length of row,.

eg.g : length of header is 80 , length of Record is 94

setting the pattern to 1,* will separate the entire string into two parts:

 H,"rest of header"  -- (1,93 [79 in use and rest white space])

R,"rest of record"- (1,93)

using this data will be ingested you use the trim function to remove the white space that will come in header and footer.

There may be other solutions also but this is what came to mind in this case

[Mkrishna]

Hi sid4u

see my input it is positional file and it has header detail and footer rows for multiple records, now i want to load into a file

1.how can i load this data into a single file

see my input (Positional file)

HA00000000051070UPAULINA             Wdavid                 john

DA00000000051070S000009003RESSupply    01012015Summer Charge   240 kwh 200 

DA00000000051070S000009003RESSupply    01012015Summer Charge   240 kwh 220

FA00000000051070S000009003M000004576ELECT1 010120150131201531

HA00000000155650UPAULINA             Wraj                 warner

DA00000000155650S000009003RESSupply    01012015Summer Charge   140 kwh 110 

DA00000000155650000009003RESSupply    01012015Summer Charge   340 kwh 300

FA00000000155650S000009003M000004576ELECT1 010120150131201531

here H is header D is detail and F is for footer field lenght is varying for header and detail and footer

[vboppudi]

Hi Krishna,

You want to load only detail records into single file?

Regards,

[vboppudi]

If you are expecting below output, you can achieve this by using tFileInputMSPositional component.

Starting job job_PositionMultiSchema at 07:52 25/04/2017.

[statistics] connecting to socket on port 3945
[statistics] connected
.----------------------------------------------------------------.
| Header |
|=--------------------------------------------------------------=|
|Data |
|=--------------------------------------------------------------=|
|HA00000000051070UPAULINA Wdavid john|
|HA00000000155650UPAULINA Wraj warner|
'----------------------------------------------------------------'

.--------------------------------------------------------------------------.
| Detail |
|=------------------------------------------------------------------------=|
|Data |
|=------------------------------------------------------------------------=|
|DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 200|
|DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 220|
|DA00000000155650S000009003RESSupply 01012015Summer Charge 140 kwh 110|
|DA00000000155650000009003RESSupply 01012015Summer Charge 340 kwh 300 |
'--------------------------------------------------------------------------'

.-------------------------------------------------------------.
| Footer |
|=-----------------------------------------------------------=|
|Data |
|=-----------------------------------------------------------=|
|FA00000000051070S000009003M000004576ELECT1 010120150131201531|
|FA00000000155650S000009003M000004576ELECT1 010120150131201531|
'-------------------------------------------------------------'

[statistics] disconnected
Job job_PositionMultiSchema ended at 07:52 25/04/2017. [exit code=0]

Regards,

[Mkrishna]

Hi Vboppudi,

No, i want to load all the header and detail and footer records into a single file

Problem is :

   1. header fields are different length , detail fields are different length and footer is also diff length.

  2. main thing is input file is positional file , not a delimited one

  3. Header  may be it has 50 fields , detail it has 20 fields, footer it has 25 fields (fields are different here)

[vboppudi]

Hi Krishna,

Means you are receiving multiple files and need to load in single file correct?

Regards,

[Mkrishna]

Hi Vboppudi,

Exactly you did in the same i did............................

instead of tlog i used tmap and tfileoutputdelimited 

tfileinputmspositional............ tmap.......tfileoutputdelimited ( for Header)

                                  ............tmap.......tfileoutputdelimited (Detail) Appen option used

                                  ............tmap.......tfileoutputdelimited (Footer) Append option used

Problem is i want to load all these header and detail and footer records in to a single file like

it is loading first Header for all the customer  than Detail for all the customer  than Footer for all the customer so on, but i need to load Heade and detail footer for one customer than header and detail footer for another customer so on

[Mkrishna]

HI vboppudi; 

you got the same out like me but i need like below this ( consider this output)

|HA00000000051070UPAULINA Wdavid john|
|DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 200|
|DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 220|
|FA00000000051070S000009003M000004576ELECT1 010120150131201531|

|HA00000000155650UPAULINA Wraj warner|
|DA00000000155650S000009003RESSupply 01012015Summer Charge 140 kwh 110|
|DA00000000155650S00009003RESSupply 01012015Summer Charge 340 kwh 300 |
FA00000000155650S000009003M000004576ELECT1 010120150131201531|

A00000000051070 is account number (length is 15)

[vboppudi]

Hi Krishna,

I used below job to create single file from multiple input files.

tPrejob--> tFiledelete used to delete target file.

Source files:

Customer1.txt

HA00000000051070UPAULINA Wdavid john
DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 200
DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 220
FA00000000051070S000009003M000004576ELECT1 010120150131201531

Customer2.txt

HA00000000155650UPAULINA Wraj warner
DA00000000155650S000009003RESSupply 01012015Summer Charge 140 kwh 110
DA00000000155650000009003RESSupply 01012015Summer Charge 340 kwh 300
FA00000000155650S000009003M000004576ELECT1 010120150131201531

Using filelist component, read two files, Used tFileInputFullRow component to read every record in file as single column and loaded this data into file.

Customers.txt

HA00000000051070UPAULINA Wdavid john
DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 200
DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 220
FA00000000051070S000009003M000004576ELECT1 010120150131201531
HA00000000155650UPAULINA Wraj warner
DA00000000155650S000009003RESSupply 01012015Summer Charge 140 kwh 110
DA00000000155650000009003RESSupply 01012015Summer Charge 340 kwh 300
FA00000000155650S000009003M000004576ELECT1 010120150131201531

Let me know if this is you expected output.

Regards,

[Mkrishna]

HI vboppudi; 

you are Right.........if i have mulitiple input files than ur solution is ok.

But here i have only single input file, having muliptle header,detail footer records for multiple customers

like H and D and F is record_type (column) and next 15 digit length is Account_no (column)

HA00000000051070UPAULINA Wdavid john
DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 200
DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 220
FA00000000051070S000009003M000004576ELECT1 010120150131201531

HA00000000155650UPAULINA Wraj warner
DA00000000155650S000009003RESSupply 01012015Summer Charge 140 kwh 110
DA00000000155650S000009003RESSupply 01012015Summer Charge 340 kwh 300
FA00000000155650S000009003M000004576ELECT1 010120150131201531

[aranax]

I'll add few images, may be that will help you understand:

 Note how I only created 2 columns in positional file 1 st for rowtype(1) and second for content(68), since its fixed length the overall length  row wont go above 69 in this case[ take max row length as precaution and trim the data afterwards]

If you wish you perform operations on any data you can use " String substring(int start, int end)"  method to extract certain data and perform calculations.

Hope it helps you.

Thanks

Aranax

[vboppudi]

Hi Krishna,

Please provide some sample data from our source and expected output. Because what i understand is, the data you provided is your source data and that data is present in your expected order like for every customer first Header, Detail and footer records. If your source data is something different like below

HA00000000051070UPAULINA Wdavid john

HA00000000155650UPAULINA Wraj warner
DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 200
DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 220
DA00000000155650S000009003RESSupply 01012015Summer Charge 140 kwh 110
DA00000000155650S000009003RESSupply 01012015Summer Charge 340 kwh 300

FA00000000051070S000009003M000004576ELECT1 010120150131201531
FA00000000155650S000009003M000004576ELECT1 010120150131201531

and you expected output is like below

HA00000000051070UPAULINA Wdavid john
DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 200
DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 220
FA00000000051070S000009003M000004576ELECT1 010120150131201531

HA00000000155650UPAULINA Wraj warner
DA00000000155650S000009003RESSupply 01012015Summer Charge 140 kwh 110
DA00000000155650S000009003RESSupply 01012015Summer Charge 340 kwh 300
FA00000000155650S000009003M000004576ELECT1 010120150131201531

Then logic will be different. Please conform this.

Regards,

[Mkrishna]

Hi Vboppudi,  this is my input i need to load into a file 

Iinput

HA00000000051070UPAULINA Wdavid john
DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 200
DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 220
FA00000000051070S000009003M000004576ELECT1 010120150131201531

HA00000000155650UPAULINA Wraj warner
DA00000000155650S000009003RESSupply 01012015Summer Charge 140 kwh 110
DA00000000155650S000009003RESSupply 01012015Summer Charge 340 kwh 300
FA00000000155650S000009003M000004576ELECT1 010120150131201531

needed output: along with input some seqno

1   HA00000000051070UPAULINA Wdavid john
1 1 DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 200
1 2 DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 220
1 1 FA00000000051070S000009003M000004576ELECT1 010120150131201531

2    HA00000000155650UPAULINA Wraj warner
2 1 DA00000000155650S000009003RESSupply 01012015Summer Charge 140 kwh 110
2 2 DA00000000155650S000009003RESSupply 01012015Summer Charge 340 kwh 300
2 1 FA00000000155650S000009003M000004576ELECT1 010120150131201531

i used tfileinputmspositional----------tmap-----tfileoutputdelimited  ( H) no append

                                            ----------tmap---=--tfileoutputdelimited (D) append

                                           -------------tmap----tfileoutputdelimited (F) append

my output is like 

HA00000000051070UPAULINA Wdavid john

HA00000000155650UPAULINA Wraj warner
DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 200
DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 220
DA00000000155650S000009003RESSupply 01012015Summer Charge 140 kwh 110
DA00000000155650S000009003RESSupply 01012015Summer Charge 340 kwh 300

FA00000000051070S000009003M000004576ELECT1 010120150131201531
FA00000000155650S000009003M000004576ELECT1 010120150131201531

[vboppudi]

Hi Krishna,

I have already provided solution for this in other post.

Regards,

[Mkrishna]

Hi vboppudi,

 See my screenshot here first all Record "H" will be loading in the output file and "D" record will be loaded and "F" record will be loading.

But my requirement is first record "H" and "D' and "F'' for one customer account and  record "H" and "D' and "F'' for second  

customer so on in the single output file

.

---

[cterenzi]

If you make each flow have a common schema, you can direct all three into tHashOutput components. Link all three hash components so the data goes to a common location. A single tHashInput into a tSort to get the desired record order, then write out your results. You may need to add some columns for sorting purposes to the three flows, then remove them before writing the file.

[vboppudi]

Hi Krishna,

please check the below data for source and target.

CUST
HA00000000051070UPAULINA Wdavid john
DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 200
DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 220
FA00000000051070S000009003M000004576ELECT1 010120150131201531
HA00000000155650UPAULINA Wraj warner
DA00000000155650S000009003RESSupply 01012015Summer Charge 140 kwh 110
DA00000000155650S000009003RESSupply 01012015Summer Charge 340 kwh 300
FA00000000155650S000009003M000004576ELECT1 010120150131201531
HA00000000155651UPAULINA Wraj warner
DA00000000155651S000009003RESSupply 01012015Summer Charge 140 kwh 110
DA00000000155651S000009003RESSupply 01012015Summer Charge 340 kwh 300
FA00000000155651S000009003M000004576ELECT1 010120150131201531
HA00000000155652UPAULINA Wraj warner
DA00000000155652S000009003RESSupply 01012015Summer Charge 140 kwh 110
DA00000000155652S000009003RESSupply 01012015Summer Charge 340 kwh 300
FA00000000155652S000009003M000004576ELECT1 010120150131201531
HA00000000155653UPAULINA Wraj warner
DA00000000155653S000009003RESSupply 01012015Summer Charge 140 kwh 110
DA00000000155653S000009003RESSupply 01012015Summer Charge 340 kwh 300
FA00000000155653S000009003M000004576ELECT1 010120150131201531

Target:
HEADER_SEQ|DETAIL_SEQ|SOURCE_RECORD
1||HA00000000051070UPAULINA Wdavid john
1|1|DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 200
1|2|DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 220
1|3|FA00000000051070S000009003M000004576ELECT1 010120150131201531
2||HA00000000155650UPAULINA Wraj warner
2|1|DA00000000155650S000009003RESSupply 01012015Summer Charge 140 kwh 110
2|2|DA00000000155650S000009003RESSupply 01012015Summer Charge 340 kwh 300
2|3|FA00000000155650S000009003M000004576ELECT1 010120150131201531
3||HA00000000155651UPAULINA Wraj warner
3|1|DA00000000155651S000009003RESSupply 01012015Summer Charge 140 kwh 110
3|2|DA00000000155651S000009003RESSupply 01012015Summer Charge 340 kwh 300
3|3|FA00000000155651S000009003M000004576ELECT1 010120150131201531
4||HA00000000155652UPAULINA Wraj warner
4|1|DA00000000155652S000009003RESSupply 01012015Summer Charge 140 kwh 110
4|2|DA00000000155652S000009003RESSupply 01012015Summer Charge 340 kwh 300
4|3|FA00000000155652S000009003M000004576ELECT1 010120150131201531
5||HA00000000155653UPAULINA Wraj warner
5|1|DA00000000155653S000009003RESSupply 01012015Summer Charge 140 kwh 110
5|2|DA00000000155653S000009003RESSupply 01012015Summer Charge 340 kwh 300
5|3|FA00000000155653S000009003M000004576ELECT1 010120150131201531

If this looks good, i will provide more details regarding code.

Regards,

[Mkrishna]

Hi vbopbudi,

      can you please explain me about this tsetglobal variable , like how it is working and what is the output of this functions. 

initially i set in tglobalvariable as : "count"=0

((integer)globalmap.put("count",((integer)globalmap.get("count"))+1));