12 Replies Latest reply: Nov 3, 2016 4:00 AM by Marius August Larsen RSS

    Crosstable script calendar issue

    Marius August Larsen

      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

         

        • Re: Crosstable script calendar issue
          Sunny Talwar

          May be this?

           

          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

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

          MålepunktnavnTemp,

          GDTNORM

          resident Graddagstallnorm;

          drop table Graddagstallnorm;

          • Re: Crosstable script calendar issue
            Avinash R

            Try like this

            Final:

            Load

            Måned,

            MålepunktnavnTemp,

            GDTNORM

            resident Graddagstallnorm;

            drop table Graddagstallnorm;

             

             

            in calender add some thing like this

             

            Month([$(_DateField)])                                             as Måned,

            • Re: Crosstable script calendar issue
              Marco Wedel

              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

                • Re: Crosstable script calendar issue
                  Marius August Larsen

                  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

                  [C:\Users\la_313mla\Desktop\Normalår.xlsx]

                  (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; 
                    • Re: Crosstable script calendar issue
                      Marius August Larsen

                      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?