topic Re: Group bys in QlikView

Group bys

Fri, 13 Sep 2013 09:51:26 GMT

Hi everyone.

I need to count the number of service hours (SH) per Service Date (SD). I have tried a host of combinations but just pasted below my latest code and result.

For some reason, Service Dates with common Service Hours are not grouping together but displaying on separate lines.

I know in Oracle I would usually say something like count (SD||SH) and so I did similar as shown in my code. I even had them separated in the code but to no avail.

Please help.

Thanks.

"Claims Summary Temp":
Load distinct monthstart([Service Date]) as SD,
date([Service Date], 'hh') as SH
Resident "Claims";

"Claims Summary":
Load SD,
         SH,
         count(SD & SH) as Totals
Resident "Claims Summary Temp"
Group By SD,
                SH;
Drop table "Claims Summary Temp";

Result:

(asterisks denotes the problem ones)

SD SH Totals

01-09-2013 11 1

01-09-2013 12 1

01-08-2013 5 1

01-08-2013 6 1 *

01-08-2013 7 1 *

Re: Group bys

jonathandienst — Fri, 13 Sep 2013 09:57:17 GMT

Ashley

I don't know if I've understood your requirement, but does this not work for you?

Load SD,

sum(SH) As SumTotal,

count(SH) as CountTotal

Resident "Claims Summary Temp"

Group By SD;

Regards

Jonathan

Re: Group bys

Fri, 13 Sep 2013 11:22:27 GMT

Hi Jonathan.

Thank you for the feedback.

Each Service Date has a timestamp. I need to count the number of occurrences of each hour of the timestamp for the same Service Date.

Example, 01-08-2013 has 9 claims that occurred during the 6^thhour of the day.

So I want every Service Date to be grouped to the first day of the month so that we can assess the counts per Month. (Therefore I have the monthstart part in my code).

So the output should be something like this for example:

Service Date SH Count

01-08-2013 05 1

01-08-2013 06 9

01-09-2013 04 10

01-09-2013 07 2

Thanks.

Re: Re: Group bys

jonathandienst — Fri, 13 Sep 2013 12:24:20 GMT

Then you need something like this:

Load date(floor(SD)) As ServiceDate,

class(Frac(SD), 1/24) As Hour,

sum(SH) As SumTotal,

count(SH) As CountTotal

Resident [Claims Summary Temp]

Group By floor(SD), class(Frac(SD), 1/24)

(check commas and brackets)

You could split the SD field into date and hours when you load Claims Summary Temp. Floor and Frac separate the date and time portions of SD, class groups the time in 1 hour intervals (1/24).

Is this closer to what you need?

Jonathan

Re: Re: Group bys

Mon, 16 Sep 2013 09:31:18 GMT

Hi Jonathan.

Apologies for the late response.

I'm getting something like this below thus far. I haven't tried out your latter suggestion as yet but the reason is because the class function doesn't seem to band in periods of one hour. So I'm trying to work on a few things using your suggestions.

Re: Group bys

sbaldwin — Mon, 16 Sep 2013 10:00:27 GMT

hi i think your problem is :

"Claims Summary Temp":

Load distinct monthstart([Service Date]) as SD,

date([Service Date], 'hh') as SH

Resident "Claims";

the date function unlike in oracle, just applies a mask, try:

"Claims Summary Temp":

Load distinct monthstart([Service Date]) as SD,

hour([Service Date]) as SH

Resident "Claims";

Thanks

Steve

Re: Group bys

Mon, 16 Sep 2013 12:05:22 GMT

Hi Steve.

Thank you for your help!

I gathered as much that it is merely masking which is why I was experiencing some issues.

I managed to come right now though. I used Jonathan's code and tweaked it a bit and all seems fine now.

Thanks!

Re: Re: Group bys

Mon, 16 Sep 2013 12:05:59 GMT

Hi Jonathan.

With some slight modifications to your code, I managed to come right.

Many thanks!

Re: Group bys

jonathandienst — Mon, 16 Sep 2013 12:08:11 GMT

The class is showing in date units. 0.041666 of day is 1 hour. Its not pretty though, so it would look better if you do it like this:

Load date(floor(SD)) As ServiceDate,

interval(class(Frac(SD), 1/24)) As Hour,

sum(SH) As SumTotal,

count(SH) As CountTotal

Resident [Claims Summary Temp]

Group By floor(SD), class(Frac(SD), 1/24)