Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Crosstable script calendar issue

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

   

12 Replies
MarcoWedel

Hi,

maybe instead of creating a separate final tablöe you should just join the Måned field:

Graddagstallnorm:

CrossTable(TempMåned, GDTNORM, 2)

LOAD Stasjon,

    MålepunktnavnTemperatur as MålepunktnavnTemp,

    Jan,

    Feb,

    Mar,

    Apr,

    Mai,

    Jun,

    Jul,

    Aug,

    Sep,

    Okt,

    Nov,

    Des

FROM.......

Left Join (Graddagstallnorm)

LOAD Distinct

          TempMåned,

          Month(Date#(TempMåned,'MMM')) as Måned

Resident Graddagstallnorm;


Drop Field TempMåned;



hope this helps


regards


Marco

Anonymous
Not applicable
Author

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; 
Anonymous
Not applicable
Author

In the script, Kontrakt table:

Kontrakt:

LOAD *,

     KontraktsPeriode&'-'&%MålepunktKey as KontraktPeriodeMålepunkt,

     %DateKey&'-'&%KontraktsgruppeKey&'-'&%MålepunktKey as New_key // (added this one) //

resident Kontrakt_t

where wildmatch(Status,'A','O','S') and not isnull([Forv./leiet.]) and [Forv./leiet.] <> 'FI'

and KontraktStartDatoFilter<=$(vToday)

and not(date(KontraktStartDato)=date(KontraktSluttDato));

Drop table Kontrakt_t;

how can we add:   %DateKey&'-'&%KontraktsgruppeKey&'-'&%MålepunktKey as New_key

without loosing data in the timehistvaktgruppe table because of the filters:

where wildmatch(Status,'A','O','S') and not isnull([Forv./leiet.]) and [Forv./leiet.] <> 'FI'

and KontraktStartDatoFilter<=$(vToday)

and not(date(KontraktStartDato)=date(KontraktSluttDato));

any suggestions?