Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
hi
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
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;
can't believe no answer...Is it tough or I am not clear?
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...