Joined: 03 Apr 2022 Posts: 10 Location: United States
Hi Friends!
I am new to the forum and need whatever help I can get to find a solution to a problem I am facing.
I have a file which I created using SORT as a comma delimited csv for reporting.
Input File looks like below, first I have a header and then the detailed records corresponding to it follows
I want to right the summary of the above records right after the detailed one for each header. Point to note here is last 3 columns in the record act as a key based on which I have to write the summary records (Write only Unique). example as below:
Code:
ABCD:
ABCD,XYX1234,XYX ,002,YES
ABCD,XYX1226,XYX ,002,YES
ABCD,XYX1226,XYX ,001,NO
ABCD,BB1241 ,BB ,001,YES
YES NO TOTAL
XYX 2 1 3
BB 1 0 1
,
EFGH:
EFGH,SA1242 ,SA ,001,YES
EFGH,XV01 ,XV ,001,YES
EFGH,X234V1 ,XV ,001,NO
YES NO TOTAL
SA 1 0 1
XV 1 1 2
I used ICETOOL to write the summary records in a separate output file, but I need to write both detailed and summary records in same file.
just to add a note here, till now I am not writing the header with (Yes/No and its Total with value). Once I have the records written to same file as detailed, I should be able to address that.
Kindly let me know if any further details are needed.
AFAIU the number of possible keys like XYX is not fixed within the same key like ABCD?
If so, you may need two-three stages of ICETOOL, or two-three job steps of DFSORT.
First, create an intermediate sums table (or "file") like this one:
Next, merge this intermediate table with your original dataset, to produce the report you need.
I personally would recommend not to use combined ICETOOL operations like SPLICE, but instead use basic DFSORT operations. This approach would allow you to better understand how the process really works, and why.
Joined: 03 Apr 2022 Posts: 10 Location: United States
sergeyken wrote:
AFAIU the number of possible keys like XYX is not fixed within the same key like ABCD?
Hi Sergeyken,
Appreciate your timely response
yes that's correct, possible keys for ABCD or others are not fixed. Some might have only 1, while some might have hundreds.
My initial report had only 3 columns - first, second and fifth i.e. ABCD,XYX1234,YES
then later was asked to add a column which will reflect the alphabets in 2nd column and how many times we have those Alphabets under Yes or No, 4th column denotes that like XYX with YES happens 2 times, and with NO only once, which I was able to do. now the latest additional requirement is to print the counters as a summary below the key ABCD.
I will try to generate the table with totals, but what I didn't understand is how we will be able to write only 1 record for XYX or BB etc horizontally. I am not much aware of how merge works, will do some reading and test it.
if you can guide me that will really help, from my end I will try it too.
Joined: 03 Apr 2022 Posts: 10 Location: United States
Joerg.Findeisen wrote:
I am able to get the following output with two DFSORT steps.
Code:
ABCD:
ABCD,XYX1234,XYX ,002,YES
ABCD,XYX1226,XYX ,002,YES
ABCD,XYX1226,XYX ,001,NO
ABCD,BB1241 ,BB ,001,YES
YES NO TOTAL
BB 1 0 1
XYX 4 1 5
EFGH:
EFGH,SA1242 ,SA ,001,YES
EFGH,XV01 ,XV ,001,YES
EFGH,X234V1 ,XV ,001,NO
YES NO TOTAL
SA 1 0 1
XV 1 1 2
Thanks Joerg. This is exactly what I need my output to look like with just one correction the counter for XYX under YES should be same as in the column which is 2.
Could you please guide me how you got it.
Joined: 03 Apr 2022 Posts: 10 Location: United States
Thanks Joerg. much appreciated.
I will try this out, will definitely have to tweak the code, since I have to add some more data (additional records) to the report in the summary. Hope it will work.
Joined: 15 Aug 2015 Posts: 1371 Location: Bamberg, Germany
The better the sample data, the better the output. Other members of the forum, as said, may have better solutions.
I am confident the addition of more fields will work, you need to imagine what fields are needed before coding to get the result you aim for.
Joined: 03 Apr 2022 Posts: 10 Location: United States
Thanks Joerg for the help.
My latest input data has changed slightly and now it has another summary detail as shown below:
Code:
ABCD:
ABCD,XYX1234,XYX ,002,YES
ABCD,XYX1226,XYX ,002,YES
ABCD,XYX1226,XYX ,001,NO
ABCD,BB1241 ,BB ,001,YES
ABCD,1234567, , ,YES
,
TOTAL YES Keys: 004 TOTAL NO: 001
SUM OF ALL YES/NO: 005
,
EFGH:
EFGH,SA1242 ,SA ,001,YES
EFGH,XV01 ,XV ,001,YES
EFGH,X234V1 ,XV ,001,NO
EFGH,9876543, , ,NO
,
TOTAL YES Keys: 002 TOTAL NO: 002
SUM OF ALL YES/NO: 004
the sort card provided by you worked well until I had those 2 summary records printed in input. I even placed an OVERLAY criteria if TOTAL or SUM found in 1st position then, it should not be part of Sections, but it keeps on writing additonal line above and below Total and Sum at 1st position.
like this as shown below:
Code:
ABCD:
ABCD,XYX1234,XYX ,002,YES
ABCD,XYX1226,XYX ,002,YES
ABCD,XYX1226,XYX ,001,NO
ABCD,BB1241 ,BB ,001,YES
YES NO TOTAL
BB 1 0 1
XYX 2 1 3
TOTA
TOTAL YES Keys: 004 TOTAL NO: 001
YES NO TOTAL
: 00
SUM OF ALL YES/NO: 005
YES NO TOTAL
/NO
Joined: 03 Apr 2022 Posts: 10 Location: United States
Hi Joerg.
New input is as below, addition to the old one is that for the keys like ABCD, EFGH, etc we could have only numeric values in 2nd column (position 6 of 7 bytes ), which will result in blanks for columns 3 and 4. But can still hold value YES/NO.
addition of 2 lines shows the summary of the detailed records, how many Total Yes/NO present including the records having space in column 3, so we have to keep those 2 lines for each key.
Hence input will be as below:
Code:
ABCD:
ABCD,XYX1234,XYX ,002,YES
ABCD,XYX1226,XYX ,002,YES
ABCD,XYX1226,XYX ,001,NO
ABCD,BB1241 ,BB ,001,YES
ABCD,1234567, , ,YES
,
TOTAL YES Keys: 004 TOTAL NO: 001
SUM OF ALL YES/NO: 005
,
EFGH:
EFGH,SA1242 ,SA ,001,YES
EFGH,XV01 ,XV ,001,YES
EFGH,X234V1 ,XV ,001,NO
EFGH,9876543, , ,NO
,
TOTAL YES Keys: 002 TOTAL NO: 002
SUM OF ALL YES/NO: 004
while the expected output should look like as below:
Code:
ABCD:
ABCD,XYX1234,XYX ,002,YES
ABCD,XYX1226,XYX ,002,YES
ABCD,XYX1226,XYX ,001,NO
ABCD,BB1241 ,BB ,001,YES
ABCD,1234567, , ,YES
,
YES NO TOTAL
BB 1 0 1
XYX 2 1 3
,
TOTAL YES Keys: 004 TOTAL NO: 001
SUM OF ALL YES/NO: 005
,
EFGH:
EFGH,SA1242 ,SA ,001,YES
EFGH,XV01 ,XV ,001,YES
EFGH,X234V1 ,XV ,001,NO
EFGH,9876543, , ,NO
,
YES NO TOTAL
SA 1 0 1
XV 1 1 2
,
TOTAL YES Keys: 002 TOTAL NO: 002
SUM OF ALL YES/NO: 004
The alphabets in 3rdcolumn derived from 2nd column is important as those shows different types of terminals (for specific business purpose) which is required for reporting.
might be better (for clarity, and efficiency) to code it as
Code:
1,6,CH,EQ,L(C'TOTAL',C'SUM OF')
Using format ZD instead of BI for counters would make the debugging of code easier. (Though, sorting of intermediate results would also need appropriate updates)
