topic Grouping hour data in QlikView

Grouping hour data

Mon, 26 Jul 2010 18:49:50 GMT

I am loading data which is to be displayed onto a pivot grid. I need to create a dimension that groups hour of day into two groups: On-Peak (hours 7-22) and Off-peak (hours 1-6 and 23-24). I was wondering if this can be done as data is loaded or do I need to create a calculated dimension on the pivot table. Here is the script I am using to populate the pivot table:

[ code ]

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='MM/DD/YYYY hh:mm';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET Periods='On-Peak;Off-Peak';

let path = 'C:\QlikView QVW\DataSources\meterData';

let outPath = 'C:\QlikView QVW\DataSources\qvd';

MeterData:

LOAD @1 as meterName,

@2 as unitName,

@3 as planName,

@4 as market,

@5 as fuelType,

timestamp#(@6,'MM/DD/YYYY h:mm') as beginDate,

month(timestamp#(@6,'MM/DD/YYYY h:mm')) as Month,

@7 as editionName,

@8*-1 as Value

FROM

$(path)*.csv

(txt, codepage is 1252, no labels, delimiter is ',', msq, header is 1 lines);

[ /code ]

Grouping hour data

Mon, 26 Jul 2010 20:20:04 GMT

You can probably use the Hour() function along with a conditional. Try:

...
If(Hour(@6) >= 7 and Hour(@6) <= 22, 1, 0) As Peak
...

Grouping hour data

stevedark — Tue, 27 Jul 2010 08:58:30 GMT

Hi there,

NMillers solution works fine in this case, but as a matter of cause I tend to do this sort of thing with an ApplyMap statement:

Map_PeekHours:
MAPPING LOAD Hour,Peek INLINE [
Hour,Peek
7,No
8,No
...
22,No];

ApplyMap('Map_PeekHours', Hour(@6), 'No') as PeekHour,

This approach will also work where you can not do a numerical comparison.

Hope that helps,
Regards,
Steve

Grouping hour data

Tue, 27 Jul 2010 14:50:05 GMT

Conditional statement proposed worked. Thanks!

Grouping hour data

Tue, 27 Jul 2010 14:53:40 GMT

Steve,

I also tested the map as to build knowledge and it also works fine.. However more work!! But I see how it can handle different data types. Thanks for the tip.

lmaurovich-

Grouping hour data

stevedark — Sat, 31 Jul 2010 23:46:17 GMT

Hi there,

ApplyMap is hugely flexible and I find it useful on most projects I work on. Using the third parameter to provide a default is a nice feature. It is also possible to nest apply map statements. You can also use ApplyMap on the data going into a MAPPING LOAD. This allows you to merge data from a whole bunch of sources into a single field.

All good stuff.
Steve