topic Re: Group By Not Working in QlikView

Group By Not Working

Anonymous — Fri, 10 Mar 2017 15:44:56 GMT

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?

Re: Group By Not Working

vishsaggi — Fri, 10 Mar 2017 15:54:29 GMT

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

Re: Group By Not Working

Anonymous — Fri, 10 Mar 2017 15:58:33 GMT

that throws me an error when trying to reload

Re: Group By Not Working

vishsaggi — Fri, 10 Mar 2017 15:59:46 GMT

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;

Re: Group By Not Working

vinieme12 — Fri, 10 Mar 2017 17:37:23 GMT

What is the output you are expecting?

Re: Group By Not Working

Anonymous — Fri, 10 Mar 2017 18:19:21 GMT

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

Re: Group By Not Working

vinieme12 — Fri, 10 Mar 2017 20:12:45 GMT

Try below

HISTORICALTICKETING:

LOAD

timeOUT,

teamNAME,

gameDATE,

sum(tixSOLDPERDAYX) as perDayCOUNT

RESIDENT RECONFIGURED_TICKETING4

Group by timeOUT, teamNAME, gameDATE;

Re: Group By Not Working

Anonymous — Fri, 10 Mar 2017 20:16:55 GMT

i still get the same result

Re: Group By Not Working

vishsaggi — Fri, 10 Mar 2017 21:35:39 GMT

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

Re: Group By Not Working

Anonymous — Mon, 13 Mar 2017 13:19:28 GMT

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

Re: Group By Not Working

Anonymous — Mon, 13 Mar 2017 13:37:10 GMT

Hi,

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
];

HISTORICALTICKETING:

LOAD
text(timeOUT) as timeOUT,
teamNAME,
gameDATE,
sum(tixSOLDPERDAYX) as perDayCOUNT
//IF(timeOUT=0, SUM(tixSOLDPERDAYX), RangeSum(SUM(tixSOLDPERDAYX), Peek('RunningTotal'))) as RunningTotal
RESIDENT RECONFIGURED_TICKETING4
Group by text(timeOUT), teamNAME, gameDATE;

DROP TABLE RECONFIGURED_TICKETING4;

// The above will remove duplicate percentages

then reload the resident table above and convert back to a numeric value:

num#(timeOUT,'#,##0%') as timeOUT

Re: Group By Not Working

Anonymous — Mon, 13 Mar 2017 13:40:49 GMT

Edited to conver text format back to %

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
];

HISTORICALTICKETING_TEMP:

LOAD
text(timeOUT) as timeOUT,
teamNAME,
gameDATE,
sum(tixSOLDPERDAYX) as perDayCOUNT
//IF(timeOUT=0, SUM(tixSOLDPERDAYX), RangeSum(SUM(tixSOLDPERDAYX), Peek('RunningTotal'))) as RunningTotal
RESIDENT RECONFIGURED_TICKETING4
Group by text(timeOUT), teamNAME, gameDATE;

DROP TABLE RECONFIGURED_TICKETING4;

HISTORICALTICKETING:
NoConcatenate
LOAD
num#(timeOUT,'#,##0%') as timeOUT,
teamNAME,
gameDATE,
perDayCOUNT
//IF(timeOUT=0, SUM(tixSOLDPERDAYX), RangeSum(SUM(tixSOLDPERDAYX), Peek('RunningTotal'))) as RunningTotal
RESIDENT HISTORICALTICKETING_TEMP;

drop Table HISTORICALTICKETING_TEMP;

Re: Group By Not Working

Kushal_Chawda — Mon, 13 Mar 2017 13:50:56 GMT

try this

HISTORICALTICKETING:

LOAD

teamNAME,

gameDATE,

Sum( distinct timeOUT) as timeOUT,

sum(tixSOLDPERDAYX) as perDayCOUNT

RESIDENT RECONFIGURED_TICKETING4

Group by teamNAME, gameDATE;

Re: Group By Not Working

jonathandienst — Mon, 13 Mar 2017 13:53:55 GMT

I think your script is OK, and the results are correct, but you have added tixSOLDPERDAYX as a dimension to the chart. That is causing the expression for 8/15/2014 to be shown twice.

Re: Group By Not Working

Anonymous — Mon, 13 Mar 2017 15:28:40 GMT

the dimension I am using is only timeOUT

Re: Group By Not Working

Anonymous — Mon, 13 Mar 2017 15:39:47 GMT

this worked great. thank you!!!