|
View previous topic :: View next topic
|
| Author |
Message |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
Input Data set:
| Code: |
Header..
Rec1...
Rec2...
Rec3...
.
.
.
.
Total Record : 1,500
ABC : 1 5 455
ABC : 2 6 1,234 |
out put expected is,
| Code: |
ABC : 1 5 455 69.67% A1
ABC : 2 6 1,234 17.74% A2 |
% calculated as 1- (455/1500) * 100
A1 calculated as if second column is <5
A2 calculated as if second column is >5 and <=6
I am thinking to,
1. Split the input dataset into two,
a. Separating single record into one dataset
| Code: |
| Total Records : 1500 << (removing ',') |
b. All ABC records only by removing ',' from last column
| Code: |
| ABC : 2 6 1,234 1234 |
2. Use joinkeys to match a and b to make division possible for calculating %
part and also use IF WHEN condition to evaluated A1 or A2 in the same
step and build accordingly.
Is there a better way? |
|
| Back to top |
|
 |
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1245
Location: Bamberg, Germany
|
|
|
|
Rohit, you are only processing the ABC records that are already in your Input dataset? I think the Total records can be pushed when found so you have all values at hand. Maybe you can shed some more light on the ABC lines for me?
With some logic build two additional columns for your selection criteria and SUM them. I think in the OUTREC or OUTFIL you could do later the required math.
Just some thoughts. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| Joerg, That is right. I am only including ABC records ignoring the rest from the input data set in my final output. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2018
Location: USA
|
|
|
|
| Rohit Umarjikar wrote: |
| Code: |
.
Total Record : 1,500
ABC : 1 5 455
ABC : 2 6 1,234 |
output expected is,
| Code: |
ABC : 1 5 455 69.67% A1
ABC : 2 6 1,234 17.74% A2 |
% calculated as 1- (455/1500) * 100
A1 calculated as if second column is <5
A2 calculated as if second column is >5 and <=6
Is there a better way? |
1) When 'Total Record' is detected PUSH it into unused position (at the end of record?) to be used in further lines re-calculations.
2) In order to choose A1/A2 based on 5-6 range use IFTHEN=(WHEN=(condition),... with BUILD= or OVERLAY=
3) In order to ignore comma-separator in 1,234 use UFF or SFF format.
4) All those build/rebuild parameters can be split between INREC/OUTREC/OUTFIL statements if cannot be done within the same statement (to simulate three sequential steps!  )
The rest of operations seems to be pretty standard, isn't it? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
Thanks Sergeyken.
I was able to get following outcome using inrec so far, but when I am doing division part in OUTREC its not giving the right results since the division results go into many decimal places.
| Code: |
ABC : 1 5 000000455 000001500
ABC : 2 6 000001234 000001500 |
So 000000455/00001500 gives 0.00000000000 which is wrong, so need to fix that part and twist it to get it right then I guess I am done.  |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2018
Location: USA
|
|
|
|
| Rohit Umarjikar wrote: |
| So 000000455/00001500 gives 0.00000000000 which is wrong, so need to fix that part and twist it to get it right then I guess I am done. |
Try to convert it not to ZD, but to either PD, or FI, or even FL/FD before trying to divide?
I may try it later if I have time for testing. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| I did a little trick to MUL by +10000000 to 000000455 and then adjusted the EDIT to get it right decimal (certainly I need to test it for all other possible values ) , now this value needs to be SUB from +1 and then MUL by 100 to get the correct % which I will give a try today. Thanks |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| I got this worked in two steps but it is fine having two step as it is easy to maintain and understand. Thanks. |
|
| Back to top |
|
|
|
|
