Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i am trying to put in a new excel crosstable to my qv application.
How can i make my application understand that Måned (month) is a Month, and connect it to my calendar. ? so when I select a value it will look up the month regardless of year.
And how can i link the field MålepunktnavnTemp to the table TimeForbrukHistTemperatur without creating a syn table or loop?
the numbers in the table i have called GDTNORM.
script:
Graddagstallnorm:
CrossTable(Måned, GDTNORM, 2)
LOAD Stasjon,
MålepunktnavnTemperatur as MålepunktnavnTemp,
Jan,
Feb,
Mar,
Apr,
Mai,
Jun,
Jul,
Aug,
Sep,
Okt,
Nov,
Des
FROM.......
Final:
Load
Måned,
MålepunktnavnTemp,
GDTNORM
resident Graddagstallnorm;
drop table Graddagstallnorm;
TimeForbrukHistTemperatur:
LOAD
Date(Floor(FBDATO - 1/24)) as %DatoTEMP,
Date(Floor(FBDATO - 1/24)) &'-'& KLOKKEN as DatoKlokkenVGTEMP,
KLOKKEN as %KlokkenTEMP,
KWHVERDI as kWhTemp,
AVRSTATUS,
MLPKTNAMN as MålepunktnavnTemperatur
FROM..
Calendar:
/* ************************************************************************************
itelligence Standard Qlik Calender Torben Seebach/Martin Didriksen Special Thanks to Rob Wunderlich and John Witherspoon
Configure Calender parameters
Syntax:
Calendar(DateField[,Prefix[,Suffix[,FullCalendar]]])
Example:
Call Calendar('[Date (NK)]','MyCal','Pre.','','true'); // creates a the MyCal table and fields are prefixed with Pre. In the superfast variat
Call Calendar('[Date (NK)]'); //creates a table called calendar based of "Date (NK)" field
Mandatory:
@_DateField Contains the name of the field to connect the calendar to
Optional:
@_CalendarName Contains the name of the calendar we create
@_CalendarPrefix A Prefix for all fields
@_CalendarSuffix A Suffix for all fields
@_FullCalendar If this contains a value then it creates a calendar with all dates from Min(Date) to Max(Date), default is the slowest option
************************************************************************************ */
Sub Calendar (_DateField, _CalendarName, _CalendarPrefix, _CalendarSuffix, _FullCalendar)
Let _StartTime = Now();
Let _CalendarName = If(Len('$(_CalendarName)')=0,'Calendar','$(_CalendarName)');
Let _CalendarPrefix = If(Len('$(_CalendarPrefix)')=0,'','$(_CalendarPrefix)');
Let _CalendarSuffix = If(Len('$(_CalendarSuffix)')=0,'','$(_CalendarSuffix)');
Let _FullCalendar = If(Len('$(_FullCalendar)')=0,1,0);
Let _DateField = PurgeChar(_DateField,'"[]');
Let _DateFormat = 'DD.MM.YYYY';
"$(_CalendarName)":
LOAD
Distinct
Date([$(_DateField)], '$(_DateFormat)' ) as [$(_DateField)],
Date([$(_DateField)], '$(_DateFormat)' ) as [$(_CalendarPrefix)Dato$(_CalendarSuffix)],
Text(Date([$(_DateField)], '$(_DateFormat)' )) as [$(_CalendarPrefix)Datotekst$(_CalendarSuffix)],
Year([$(_DateField)]) as [$(_CalendarPrefix)År$(_CalendarSuffix)],
Year([$(_DateField)])&'-'&Month([$(_DateField)]) as [$(_CalendarPrefix)År-Måned$(_CalendarSuffix)],
week([$(_DateField)])&'-'&Year([$(_DateField)]) as [$(_CalendarPrefix)Uke-År$(_CalendarSuffix)],
week([$(_DateField)]) as [$(_CalendarPrefix)Uke$(_CalendarSuffix)],
Month([$(_DateField)]) as [$(_CalendarPrefix)Måned$(_CalendarSuffix)],
//Hour([$(_DateField)])&'-'&Minute([$(_DateField)]) as [$(_CalendarPrefix)Klokken$(_CalendarSuffix)],
Num(Month([$(_DateField)])) as [$(_CalendarPrefix)Månedsnummer$(_CalendarSuffix)],
Day([$(_DateField)]) as [$(_CalendarPrefix)Dag$(_CalendarSuffix)],
WeekDay([$(_DateField)]) as [$(_CalendarPrefix)Ukedag$(_CalendarSuffix)],
Year([$(_DateField)])&'-'&Text(Date([$(_DateField)],'MM')) as [$(_CalendarPrefix)År-måned$(_CalendarSuffix)],
'Q'&Ceil(Month([$(_DateField)])/3) as [$(_CalendarPrefix)Kvartal$(_CalendarSuffix)],
AutoNumber(MonthStart([$(_DateField)]),'_MonthSerial') as [$(_CalendarPrefix)Månedsløpenummer$(_CalendarSuffix)],
AutoNumber(QuarterStart([$(_DateField)]),'_QuarterSerial') as [$(_CalendarPrefix)Kvartalsløpenummer$(_CalendarSuffix)],
AutoNumber(weekyear([$(_DateField)]) &'|'&week([$(_DateField)]),'_WeekSerial') as [$(_CalendarPrefix)Ukeløpenummer$(_CalendarSuffix)] ;
If _FullCalendar=1 Then
Load Num(_DateStart+(Iterno()-1)) as [$(_DateField)]
While (_DateStart+(Iterno()-1)<=_DateStop);
LOAD
Floor(Min(Fieldvalue('$(_DateField)',RecNo()))) as _DateStart,
Floor(Max(Fieldvalue('$(_DateField)',RecNo()))) as _DateStop
AUTOGENERATE FieldValueCount('$(_DateField)');
Else
LOAD Num(Fieldvalue('$(_DateField)',RecNo())) as [$(_DateField)]
AUTOGENERATE FieldValueCount('$(_DateField)');
End If
/* clean variables */
Let _TotalTime = Round((Now()-_StartTime)*60*60*24,0.00000000001);
Let _StartTime = NULL;
Let _vDateStart = NULL;
Let _vDateStop = NULL;
Trace $(_CalendarName) created in: $(_TotalTime) seconds;
Let _TotalTime = NULL;
end sub
Thanks, but it didnt work.. /:
I don't think the Måned field is creating the loop ...you need to deal with the other table fields 
share the complete data model view so that we could suggest on the same
