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
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
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.
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.
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
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
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,
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)
Hi Krishna,
Means you are receiving multiple files and need to load in single file correct?
Regards,
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
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)
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,
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
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
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,
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
Hi Krishna,
I have already provided solution for this in other post.
Regards,
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
.
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,
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));