Remove duplicates from Temp table.

[prashanth]

Can anyone please help me out with the below scenario.

Def temp-table tt-temp
field a as int
field b as int
field c as int
field dt as date.

Consider the below records in the above TT.

Initial records.

a b c dt
99 5 10 4/12/2012
100 6 12 5/12/2012
100 6 14 6/12/2012
100 6 10 7/12/2012
101 7 16 8/12/2012



What can i do, so that i end up having the records as below i.e., If there are two/more records having similar values in the fields 'a' and 'b', then it should be merged to a single record where a and b will remain the same c will be added up (in the above case c should be 12+14+10 = 36).

Final record list.

a b c dt
99 5 10 4/12/2012
100 6 36 7/12/2012
101 7 16 8/12/2012.

Please let me know if i have to furnish anymore details.
Thanks in advance.

Progress version - 10.2b

 

[daranroo7]

Can anyone please help me out with the below scenario.

Def temp-table tt-temp
field a as int
field b as int
field c as int
field dt as date.

Consider the below records in the above TT.

Initial records.

a b c dt
99 5 10 4/12/2012
100 6 12 5/12/2012
100 6 14 6/12/2012
100 6 10 7/12/2012
101 7 16 8/12/2012



What can i do, so that i end up having the records as below i.e., If there are two/more records having similar values in the fields 'a' and 'b', then it should be merged to a single record where a and b will remain the same c will be added up (in the above case c should be 12+14+10 = 36).

Final record list.

a b c dt
99 5 10 4/12/2012
100 6 36 7/12/2012
101 7 16 8/12/2012.

Please let me know if i have to furnish anymore details.
Thanks in advance.

Progress version - 10.2b

Prashanth,
The way which i can see here is, Group the temp-table records by a and b fields (that is break by). then "accumulate" the value of "c" for each record in the temp-table.
IF last-of() loop, create another temp-table and store the "Accum" value of "c" there which will end up in creating single record with the total value.
Hope this helps.

Regards,
Daniel Ranjit.R

 

[stokefc22]

Could u not do something along the lines of....

find first  tt-temp where
             tt-temp.a = intVarA and 
             tt-temp.b = intVarB
             no-error.

if not available tt-temp then
do:
     create tt-temp.
     assign tt-temp.a = intVarA
              tt-temp.b = intVarB.
end.


assign tt-temp.c = intVarC.

if tt-temp.dt = ? or tt-temp.dt < dtVar then
     assign tt-temp.dt = dtVar.

when creating the tt records.

 

[prashanth]

Records would have already been created, and these created values are used at many places. I just wanted this change to happen at only 1 place.

 

[stokefc22]

Ok well perhaps go with what Daniel is suggesting then. If you are doing something relatively with the values suppose u could get away without creating a new tt otherwise your going to need a new 'summary' tt...

 

[FrancoisL]

Here is your homework =p

DEF VAR iCount AS INT.

FOR EACH tt-temp 
   BREAK BY tt-temp.a BY tt-temp.b :
   IF FIRST-OF(tt-temp.b) THEN iCount = 0.

   iCount = iCOunt + tt-temp.c.

  IF LAST-OF(tt-temp.b) THEN
     tt-temp.c = iCount.
  ELSE 
     DELETE tt-temp.

END.

 

[prashanth]

Here is your homework =p

DEF VAR iCount AS INT.

FOR EACH tt-temp 
   BREAK BY tt-temp.a BY tt-temp.b :
   IF FIRST-OF(tt-temp.b) THEN iCount = 0.

   iCount = iCOunt + tt-temp.c.

  IF LAST-OF(tt-temp.b) THEN
     tt-temp.c = iCount.
  ELSE 
     DELETE tt-temp.

END.

This works. Thanks!
But consider one more record(a=100,b=6,c=20,dt=10/10/2012) at the end.

Initial records.

a b c dt
99 5 10 4/12/2012
100 6 12 5/12/2012
100 6 14 6/12/2012
100 6 10 7/12/2012
101 7 16 8/12/2012
100 6 20 10/10/2012

My final Temp-table should hold values as below.

a b c dt
99 5 10 4/12/2012
100 6 36 7/12/2012
101 7 16 8/12/2012
100 6 20 10/10/2012.

It should work only if they are consecutive records. Break by might not work here.

 

[FrancoisL]

It should work only if they are consecutive records. Break by might not work here.

Of course it won't work lol , You never said to group only consecutive records . There is big difference between combining records with the same keys and records with the same key that are consecutive.

There are many ways of doing it but you can't use BREAK BY because you need to keep the order. You'll need to do the FIRST-OF and LAST-OF manually.

 

[prashanth]

Thank You!