Group By Not Working

[Anonymous]

Hello:  I have a data set that shows up like this:

teamNAME	gameDATE	timeOUT	tixSOLDPERDAYX
vs. Team L	8/15/2014 0:00	66%	4
vs. Team L	8/15/2014 0:00	67%	12
vs. Team L	8/15/2014 0:00	68%	2
vs. Team L	8/15/2014 0:00	69%	0
vs. Team L	8/15/2014 0:00	69%	2
vs. Team L	8/15/2014 0:00	70%	0
vs. Team L	8/15/2014 0:00	71%	2
vs. Team L	8/15/2014 0:00	72%	15
vs. Team L	8/15/2014 0:00	73%	0

i think use this transformation:

HISTORICALTICKETING:

LOAD 

  timeOUT,

  teamNAME,

  gameDATE,

  sum(tixSOLDPERDAYX) as perDayCOUNT,

  IF(timeOUT=0, SUM(tixSOLDPERDAYX), RangeSum(SUM(tixSOLDPERDAYX), Peek('RunningTotal'))) as RunningTotal

RESIDENT RECONFIGURED_TICKETING4

Group by timeOUT, teamNAME, gameDATE;

DROP TABLE RECONFIGURED_TICKETING4;

I was trying to utilize the group by function to eliminate the duplicate values (like 69% above). however the output still shows two 69% values. This is problematic because i am showing an average of Team L RunningTotal, which is double counting these values.

Is there something wrong with my group By? Or is there a way to write something to eliminate the duplicate values?

[vishsaggi]

Here your timeOUT is never 0 as they all have %ges.

HISTORICALTICKETING:

LOAD

  timeOUT,

  teamNAME,

  gameDATE,

  sum(tixSOLDPERDAYX) as perDayCOUNT,

  IF(timeOUT=0, SUM(tixSOLDPERDAYX), RangeSum(SUM(tixSOLDPERDAYX), Peek('RunningTotal'))) as RunningTotal

RESIDENT RECONFIGURED_TICKETING4

Group by timeOUT, teamNAME, gameDATE;

DROP TABLE RECONFIGURED_TICKETING4;

May be this you are looking for?

LOAD

  timeOUT,

  teamNAME,

  gameDATE,

  sum(tixSOLDPERDAYX) as perDayCOUNT,

  IF(tixSOLDPERDAYX =0, SUM(tixSOLDPERDAYX), RangeSum(SUM(tixSOLDPERDAYX), Peek('RunningTotal'))) as RunningTotal

[Anonymous]

that throws me an error when trying to reload

[vishsaggi]

Try this?

RECONFIGURED_TICKETING4:

LOAD * INLINE [

teamNAME, gameDATE, timeOUT, tixSOLDPERDAYX

vs. Team L, 8/15/2014 0:00, 66%, 4

vs. Team L, 8/15/2014 0:00, 67%, 12

vs. Team L, 8/15/2014 0:00, 68%, 2

vs. Team L, 8/15/2014 0:00, 69%, 0

vs. Team L, 8/15/2014 0:00, 69%, 2

vs. Team L, 8/15/2014 0:00, 70%, 0

vs. Team L, 8/15/2014 0:00, 71%, 2

vs. Team L, 8/15/2014 0:00, 72%, 15

vs. Team L, 8/15/2014 0:00, 73%, 0

];

NoConcatenate

History:

LOAD

  timeOUT,

  teamNAME,

  gameDATE,

  sum(tixSOLDPERDAYX) as perDayCOUNT,

  IF(tixSOLDPERDAYX = 0, RangeSum(SUM(tixSOLDPERDAYX), Peek('RunningTotal')), SUM(tixSOLDPERDAYX)) as RunningTotal

RESIDENT RECONFIGURED_TICKETING4

Group by timeOUT, teamNAME, gameDATE, tixSOLDPERDAYX;

DROP TABLE RECONFIGURED_TICKETING4;

[vinieme12]

What is the output you are expecting?

[Anonymous]

I want the same output that i have, just only one value for each timeOUT- like this:

teamNAME	gameDATE	timeOUT	tixSOLDPERDAYX
vs. Team L	8/15/2014 0:00	66%	4
vs. Team L	8/15/2014 0:00	67%	12
vs. Team L	8/15/2014 0:00	68%	2
vs. Team L	8/15/2014 0:00	69%	2
vs. Team L	8/15/2014 0:00	70%	0
vs. Team L	8/15/2014 0:00	71%	2
vs. Team L	8/15/2014 0:00	72%	15
vs. Team L	8/15/2014 0:00	73%	0
vs Team L	8/15/2014 0:00	74%	5

[vinieme12]

Try  below

HISTORICALTICKETING:

LOAD

  timeOUT,

  teamNAME,

  gameDATE,

  sum(tixSOLDPERDAYX) as perDayCOUNT

RESIDENT RECONFIGURED_TICKETING4

Group by timeOUT, teamNAME, gameDATE;

[Anonymous]

i still get the same result

[vishsaggi]

Did you try Vineeth's script? You should get only one timeOUT value 69%.

[Anonymous]

It still loads the exact same.  I also am not sure how that script is any different from what i had below, with the exception of the running total