find the lowest value in a csv file



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

find the lowest value in a csv file

Postby gyt3 » Wed Feb 13, 2013 11:35 pm

Am facing problem while sorting a CSV file
My input file looks like this.Input Lrecl is 80.
id,wages,day
a12345,345,monday,
a12345,345,monday,
a12345,567890,monday,
a12345,345,tuesday,
a12345,34500,monday,
b12345,345,monday,
b12345,34,monday,

I need only those records with least wages in one output file and the records which are rejected in another output file

My output file 1 ( Only Least Wages )

a12345,345,monday
a12345,345,tuesday
b12345,34,monday

MY Output file 2 (Rejected Records)

a12345,34500,monday,
a12345,567890,monday,
b12345,345,monday,

Can anyone suggest me how to do it using sort.

If i sort the entire record and give xsum ,am getting wrong output like this

Output1:
a12345,345,monday,
a12345,567890,monday,
a12345,345,tuesday,
a12345,34500,monday,
b12345,345,monday,
b12345,34,monday,

Output2 :
a12345,345,monday,
gyt3
 
Posts: 15
Joined: Sun Dec 16, 2012 9:58 am
Has thanked: 3 times
Been thanked: 0 time

Re: find the lowest value in a csv file

 

Re: find the lowest value in a csv file

Postby NicC » Thu Feb 14, 2013 12:05 am

Which is "least wages"? If the 2nd field why do you include 345 in your output when the smallest value is 34?

Further from the DFSort manual:
DFSORT does not support the XSUM parameter provided by a competitive sort

So why are you trying to use it. Check the manual for correct syntax. If you are using that other sort product (SYNCSORT with WER message identifiers) then let us know and we will move the topic to the correct place.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic

These users thanked the author NicC for the post:
gyt3 (Thu Feb 14, 2013 1:07 pm)
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: find the lowest value in a csv file

Postby gyt3 » Thu Feb 14, 2013 8:43 am

Hi Nic,

I need lowest wages for every unique ID and day combination.
a12345,345,monday,
a12345,345,monday,
a12345,567890,monday,
a12345,345,tuesday,
a12345,34500,monday,
b12345,345,monday,
b12345,34,monday,

so for id - a12345 and day monday the lowest wages is 345
for id - a12345 and day tuesday the lowest wages is 345
for id - b12345 and day monday , thelowest wages is 34

I will try with syncsort today too.Thanks for your advice.
gyt3
 
Posts: 15
Joined: Sun Dec 16, 2012 9:58 am
Has thanked: 3 times
Been thanked: 0 time

Re: find the lowest value in a csv file

Postby BillyBoyo » Thu Feb 14, 2013 1:01 pm

You need to look at PARSE to be able to build a sort key from those variable lengths/positions.

You then SORT on the keys.

You then use IFTHEN=(WHEN=GROUP

Or, you look at the DFSORT Smart Tricks publication to see how XSUM, and more, can be achieved.

If you need assistance, please post the full sysout of what you have attempted, in the Code tags.

These users thanked the author BillyBoyo for the post:
gyt3 (Thu Feb 14, 2013 1:07 pm)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post