I'm used to sorting for some time now, but I have a new challenge for which I found no compact answer myself. Please can you guys suggest me on how to do this? Below is the problem statement.
I have an input file(VB) as below:
00 Header
10 Account1 Name
20 Flag1 Flag2
30 Amount spent
10 Account2 Name
20 Flag1 Flag2
30 Amountspent2
99 Trailer
10,20,30 are the record types that denote a single account. Some records may have 40 and 50 as well. But 10 is always the starting record for each account.
My condition for extraction is,
If Name = 'XXXX' and Flag1 ='Y' and say the Account 2 matches this condition my output file should have all the records specific to that accout(10,20,30), and not just the matching records(10,20).
Hope I'm clear in explaining my problem. Let me know otherwise.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
karthickpearl wrote:
Hope I'm clear in explaining my problem. Let me know otherwise.
Not really. Since we are dealing with VB files it is quite important to mention the positions and length of the fields to evaluated. Also does every account (rectype =10) have a corresponding rectype=20 ? can there be any instances where you don't have rectype=20 record?
Also does every account (rectype =10) have a corresponding rectype=20 ? can there be any instances where you don't have rectype=20 record?
Yes, As mentioned in above, Rectype 10 and 20 will always be mandatory, but 30 & 40 is optional. And there are cases where I would need to evaluate a field in 30 or 40 if present.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
karthickpearl wrote:
[...]
Quote:
Also does every account (rectype =10) have a corresponding rectype=20 ? can there be any instances where you don't have rectype=20 record?
Yes, As mentioned in above, Rectype 10 and 20 will always be mandatory, but 30 & 40 is optional. And there are cases where I would need to evaluate a field in 30 or 40 if present.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Pearl,
Thanks. Sorry, I failed to use Preview and pickled the nested quote.
What I want to know is about the 30/40-type records. If you are going to do something with them, we may need to know, because otherwise you may break a solution, which will waste someone's time.
10,20,30 are the record types that denote a single account. Some accounts may have 40 and 50 as well. But 10 is always the starting record for each account.
Actually my last explanation is just a variant of this but with the same meaning.
May be I should have tried to present my problem with more apt words.
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
Maybe you should post some actual sample input data that covers the various situations that can occur as well as the output you want when the sample input is processed.
Explanation of input file:
The input file contains details of companies. The details of each company start at rectype-10 and end before next rectype-10 record.
Rectype10 - Carries companies names
Rectype20 - Carries address details of the company
Rectype30 - Carries details of products.(Note that not all companies will have this detail, since there are companies which just renders services and products arent applicable)
Rectype40 - Carries products price details.(will be available only when rectype 30 is available)
Filtering condition:
Company name = "IBM" & Productname = "Rationalrose"
Note: My filtering conditions will span across record types. If the conditions are satisfied then the whole set of records available for that company should show up in my output file.
In the above example the conditions are pointed in Rectype 10 and 30. But my output file will have all the record types available for that company when these conditions are matched.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
karthickpearl wrote:
My condition for extraction is,
If Name = 'XXXX' and Flag1 ='Y' and say the Account 2 matches this condition my output file should have all the records specific to that accout(10,20,30), and not just the matching records(10,20).
Hope I'm clear in explaining my problem. Let me know otherwise.
karthickpearl wrote:
Hi Dick,
Filtering condition:
Company name = "IBM" & Productname = "Rationalrose"
Seriously ? I am not sure how a 1 byte flag can contain the word 'Rationalrose'
And you haven't even provided the position and length of the field which contains the product name. oh well here is a jcl which will give you the desired results based on your initial post. I assumed that your company name is 'xxxx' and the flag ='y'
Sorry, I missed stating the field position and length earlier.
I have a restriction and hence could not share the data that I have. I've created an equivalent scenario.
Inputfile(VB):
Code:
0025/03/2014
101001IBM
2040 clarie street Branford Florida 32008
30rationalrose
30DB2
30IMS
101002CISCO
20170 West Tasman Dr. San Jose CA 95134
30webex
30IPS
101003ACCENTURE
2099 Mccullum street hyderabad AP 553001
9925/03/2014011
Filtering conditions:
company name='IBM' and one of its products is 'DB2'
(OR)
ADDR5 = '95134'
Output file(VB):
Code:
0025/03/2014
101001IBM
2040 clarie street Branford Florida 32008
30rationalrose
30DB2
30IMS
101002CISCO
20170 West Tasman Dr. San Jose CA 95134
30webex
30IPS
9925/03/2014009
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
KarthickPearl,
It is tough to provide a solution for a moving target. Earlier you wanted to validate just record type 10 & 20. Now you also need to evaluate record type 30 and you need the header and trailer too ?
Oh well You already have a sample and you need to modify it.
Hint : Using IFTHEN statement validate the fields you wanted to check and update 2 flags in JNF2CNTL and you are all set.