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
not working...
when i reload, i dont get error. But the tables wont update... and all my tables in transform disapear.
Graddagstallnorm:
CrossTable(TempMåned, GDTNORM, 2)
LOAD Stasjon,
MålepunktnavnTemperatur,
Jan,
Feb,
Mar,
Apr,
Mai,
Jun,
Jul,
Aug,
Sep,
Okt,
Nov,
Des
FROM
(ooxml, embedded labels, table is Ark1);
| Left Join (Graddagstallnorm) | |
| LOAD Distinct | |
| TempMåned, | |
| Month(Date#(TempMåned,'MMM')) as Måned | |
| Resident Graddagstallnorm; | |
| Drop Field TempMåned; |