topic Re: Calendar Hours Problem in QlikView

Calendar Hours Problem

Mon, 28 Jan 2013 18:19:29 GMT

Hi Guys,

I have a FactTable with following HourlyData and it varies for CalendarHrs [01 to 24] and FiscalHrs [06 to 05]:

In my Qlikview App, I have named the Hours according to FiscalHrs in my Facttable.

CalenderHrs	FiscalHrs
01	06
02	07
03	08
04	09
05	10
06	11
07	12
08	13
09	14
10	15
11	16
12	17
13	18
14	19
15	20
16	21
17	22
18	23
19	24
20	01
21	02
22	03
23	04
24	05

I have a report based on the FiscalHrs and when I select NETZT = NW, I get 14 to 24 Hrs and this is correct. When I select NETZT = SW, I get 06 to 14 and 01 to 05. Customer don't want to see 14,01,02,03,04,05 as 14 as already been in NETZT = NW and 01 to 05 belong to another day.

Can anyone help how to solve it. Got confused. Seems simple but It is not... Attached is the example.

Thanks
Sravan

Re: Calendar Hours Problem

Tue, 29 Jan 2013 07:21:56 GMT

To explain the problem in detail:

I have named my facttable with Hrs as follows:

Facttable_temp:
Crosstable (Stunde [Hour], Wert [Value] )
Load
      ID1,
      ID2,
      Date,
     TW1 as '06',
     TW2 as '07',
     TW3 as '08',
     TW4 as '09',
     TW5 as 10,
     TW6 as 11,
     TW7 as 12,
     TW8 as 13,
     TW9 as 14,
     TW10 as 15,
     TW11 as 16,
     TW12 as 17,
     TW13 as 18,
     TW14 as 19,
     TW15 as 20,
     TW16 as 21,
     TW17 as 22,
     TW18 as 23,
     TW19 as 24,
     TW20 as '01',
     TW21 as '02',
     TW22 as '03',
     TW23 as '04',
     TW24 as '05'
From....
;
FactTable:
Load
ID1 & Date & Stunde as Key1...,
ID2 & Date & Stunde as Key2
Date & Stunde as Date,
Stunde,
Day
Resident Facttable_temp;
Drop table Facttable_temp;

and I have the NETZT.NETZT = SW with NETZT.GUELTIG_VON_VARIABLE_MS [NETZT.VALIDFROM] as 2010111818 [YYYYMMDDhh] to NETZT.GUELTIG_BIS_VARIABLE_MS [NETZT.VALIDTO] as 2011050914 [YYYYMMDDhh]

The Date is connected to Calendar [Year, Month, Day etc..] which is in Format YYYYMMDDhh.

There is a intervalmatch between Date and NETZT.GUELTIG_VON_VARIABLE_MS and NETZT.GUELTIG_BIS_VARIABLE_MS and I get the values in calendar for the above example when I select Year = 2011

Month = May [05], Day= 09, NETZT.NETZT = SW as follows in calendar

2011050901

2011050902

2011050903

2011050904

2011050905

2011050906

2011050907

2011050908

2011050909

2011050910

2011050911

2011050912

2011050913

2011050914

but actually according to my need, I need only values from and to as my FiscalHrs start from 06 and ends at 05

2011050906

2011050907

2011050908

2011050909

2011050910

2011050911

2011050912

2011050913

2011050914

How can I make my Calendar think that the date counts like this:

2011050906

2011050907

2011050908

2011050909

2011050910

2011050911

2011050912

2011050913

2011050914

2011050915

2011050916

2011050917

2011050918

2011050919

2011050920

2011050921

2011050922

2011050923

2011050924

2011051001

2011051002

2011051003

2011051004

2011051005

Hope someone can help me. Its a lot tricky to do I guess...

Thanks in Advance

Sravan

Re: Calendar Hours Problem

Tue, 29 Jan 2013 07:49:02 GMT

I generated calendar like this but it seems to be wrong

LET qDateMin = Num(MakeDate(2010,10,01)) -1;
LET qDateMax = Num(Today());

    FOR i = 1 to 24        // Hour 0 to 23
    TempCalendar:
    LOAD date($(qDateMin) + IterNo()) as TempDate,
    num($(i),'00') as Hour
    AUTOGENERATE 1
    WHILE $(qDateMin) + IterNo() <= $(qDateMax)
    ;
NEXT i
Calendar:
load
    Date(TempDate,'YYYYMMDD')& Hour as GASTAG,
    TempDate,
    Hour,
    If(num(Month(TempDate)) >= '10', 'GWJ ' & Right(Year(TempDate),2) &'/'& Right((Year(TempDate)+1),2) , 'GWJ ' & Right(Year(TempDate)-1,2) &'/'& Right((Year(TempDate)),2)) as FISCALYEAR,

    num(Day(TempDate),'00')                     as Day,         //CalendarDayOfMonth,
    Month(TempDate)                 as Month,         //CalendarMonthName,
//    'Q' & Ceil(Month(TempDate)/3)     as Quartal,
    Year(TempDate)                     as KY, //CalendarYear,
    //###Calendar Date Names###
    WeekName(TempDate)                 as Kalenderwoche //CalendarWeekNumberAndYear,

resident
     TempCalendar
//order by
//    TempDate ASC
    ;
DROP TABLE TempCalendar;

Re: Calendar Hours Problem

Tue, 29 Jan 2013 14:34:05 GMT

can't believe no answer...Is it tough or I am not clear?

Re: Calendar Hours Problem

RedSky001 — Tue, 29 Jan 2013 14:53:18 GMT

I don't understand what you are trying to do.

The qvw. you uploaded is hard to understand as all the field names are in German and I don't speak / read German. I'm guessing that put a lot of people off helping as this is an English speaking forum. The subsequent replies just confused me further.

Maybe you could start again with an example of the data you have. Then what translations you want to occur and how you expect the data to look at the end?

I still might not be able to help but I'm sure someone could...

CalenderHrs	FiscalHrs
01	06
02	07
03	08
04	09
05	10
06	11
07	12
08	13
09	14
10	15
11	16
12	17
13	18
14	19
15	20
16	21
17	22
18	23
19	24
20	01
21	02
22	03
23	04
24	05

CalenderHrs	FiscalHrs
01	06
02	07
03	08
04	09
05	10
06	11
07	12
08	13
09	14
10	15
11	16
12	17
13	18
14	19
15	20
16	21
17	22
18	23
19	24
20	01
21	02
22	03
23	04
24	05

CalenderHrs	FiscalHrs
01	06
02	07
03	08
04	09
05	10
06	11
07	12
08	13
09	14
10	15
11	16
12	17
13	18
14	19
15	20
16	21
17	22
18	23
19	24
20	01
21	02
22	03
23	04
24	05