topic Re: time groups, game durration in QlikView

time groups, game durration

ilanbaruch — Wed, 04 Mar 2015 07:31:50 GMT

Hi all,

I'm looking for a way to group gamers by game duration time.

in my script I have a calculate field 'GameDuration' :

Date(EndTime - StartTime, 'hh:mm:ss') as GameDuration

my groups calculated in the same load statement :

if( GameDuration <'00:03:00','0-3',If(GameDuratione >='00:03:00' and GameDuratione <'00:06:00','3-6','6+')) as DurationGroup

I get all kind of errors..

any ideas??

Re: time groups, game durration

Anonymous — Wed, 04 Mar 2015 08:11:31 GMT

ilan,

Best way to do this is by using function IntervalMatch. This will help IntervalMatch

Marc.

Re: time groups, game durration

hic — Wed, 04 Mar 2015 09:09:44 GMT

How and where do you want to define your groups? The answer affects which solution to choose...

A couple of tips:

Use EndTime - StartTime as GameDuration during the development instead. Then you will get the duration as a number (in days). When you're done, you can format it as Interval(EndTime - StartTime,'hh:mm:ss') as GameDuration to make it prettier.
Read Buckets

HIC

Re: time groups, game durration

rubenmarin — Wed, 04 Mar 2015 09:14:03 GMT

Hi Ilan, Time and date funcions returns a number (with an string interpretation), if you want to compare in minutes you can declare a variable with the value of a minute and use it in the 'if' comparison:

LET vsMinute = Replace(1/1440, ',', '.'); //For me ',' is dec separator, to compare in if I need to change to '.'

if( GameDuration <$(vsMinute)*3,'0-3',If(GameDuratione >=$(vsMinute)*3 and GameDuratione <$(vsMinute)*6,'3-6','6+')) as DurationGroup

Re: time groups, game durration

Wed, 04 Mar 2015 09:22:59 GMT

Hi,

Did you copy past the expression from your script?

if( GameDuration <'00:03:00','0-3',If(GameDuratione >='00:03:00' and GameDuratione <'00:06:00','3-6','6+')) as DurationGroup

Change to

if( GameDuration <'00:03:00','0-3',If(GameDuration >='00:03:00' and GameDuration <'00:06:00','3-6','6+')) as DurationGroup

Also if you are writing this in the same load statement it would not work as GameDuration would not be available. You either need to reload the table or subsitute GameDuration with the formula some thing like:

if( (Date(EndTime - StartTime, 'hh:mm:ss')) < '00:03:00','0-3',If((Date(EndTime - StartTime, 'hh:mm:ss')) >= '00:03:00' and (Date(EndTime - StartTime, 'hh:mm:ss')) < '00:06:00','3-6','6+')) as DurationGroup

Regards

Re: time groups, game durration

PrashantSangle — Wed, 04 Mar 2015 09:31:30 GMT

Hi,

If you are creating GameDuration in same load statement and in same load statement you are using GameDuration Field It wont work.

For using this newly created field you have to use resident load.

For Example:

Test:

Load *

Interval(EndTime - StartTime,'hh:mm:ss') as GameDuration

from tableName;

Final:

Load *

if(GameDuration<'00:03:00','0 - 3 Min', if(GameDuration>='00:03:00' and GameDuration>='00:06:00'),'0 - 6 Min ','6 +')) as DurationGroup

Resident Test;

Drop table Test;

Note: Also Read Bucket article written by Henric, It will help you.

Regards

Re: time groups, game durration

ilanbaruch — Thu, 05 Mar 2015 09:09:44 GMT

Thank you.