14 Replies Latest reply: Feb 11, 2016 11:59 AM by Erdal Cücen RSS

    Date

    Erdal Cücen

      Hello,

       

      I would like the following date format "001.2016" divided in month and year. -> 001 and 2016 or 1 and 2016

       

      Name of the dimension is DATAEGV_PERIODE


      The data comes in this form:

       

      001.2016

      002.2016

      .....

      012.2016

       

      Does anyone have this idea?

       

      Thanks in advance.

       

      Erdal

        • Re: Date
          Stefan Wühl

          Something like

           

          LOAD *,

                    Month(YearMonth) as Month,

                    Year(YearMonth) as Year;

          LOAD *,

                    MakeDate( Right(DATAEGV_PERIODE,4), Left(DATAEGV_PERIODE,3) ) as YearMonth;

          LOAD DATAEGV_PERIODE

          FROM YourTable;

            • Re: Date
              Erdal Cücen

              Hi,

               

              i have tried with this code:

               

              LOAD   DATAEGV_PERIODE

               

              FROM

              W:\Controlling\12_QlikView\Data_BlueBooklet.xlsx

              (ooxml, embedded labels);

               

              LOAD   MonthName(Left(DATAEGV_PERIODE,3)) as Month,

                     YearName(Right(DATAEGV_PERIODE,4)) as Year

               

              FROM W:\Controlling\12_QlikView\Data_BlueBooklet.xlsx

              (ooxml, embedded labels);

               

               

              LOAD   MakeDate( Right(DATAEGV_PERIODE,4), Left(DATAEGV_PERIODE,3) ) as YearMonth

               

               

              FROM W:\Controlling\12_QlikView\Data_BlueBooklet.xlsx

              (ooxml, embedded labels);

               

               

              The output is confused:

               

               

               

              Do you have any idea?

               

              Thanks

              Erdal

               

               

              Von: swuehl

              Gesendet: Mittwoch, 10. Februar 2016 18:08

              An: Cücen, Erdal, NMM-FF <Erdal.Cuecen@S4M.COM>

              Betreff: Re:  - Date

               

               

               

               

                • Re: Date
                  Stefan Wühl

                  You've used

                   

                   

                  LOAD   MonthName(Left(DATAEGV_PERIODE,3)) as Month,

                         YearName(Right(DATAEGV_PERIODE,4)) as Year

                   

                  FROM W:\Controlling\12_QlikView\Data_BlueBooklet.xlsx

                  (ooxml, embedded labels);

                   

                   

                  Here, Monthname, and YearName functions expect a Date as argument, not the Year resp. Month value.

                  Check how I've first interpreted the date, then how I've created Year and Month from that date in a so called preceding load.

                    • Re: Date
                      Erdal Cücen

                      Hi,

                       

                      many thaks,

                       

                      i have solved with:

                       

                       

                      Date#(left(right(DATAEGV_PERIODE,7),2),'MM') as Monat,

                       

                       

                      Date#(right(DATAEGV_PERIODE,4),'YYYY') as Jahr

                       

                       

                      Regards,

                      Erdal

                       

                       

                      Von: swuehl

                      Gesendet: Mittwoch, 10. Februar 2016 20:46

                      An: Cücen, Erdal, NMM-FF <Erdal.Cuecen@S4M.COM>

                      Betreff: Re:  - Date

                       

                       

                       

                       

                • Re: Date
                  Srikanth P

                  You can try MakeDate(subfield(FieldName,'.',2) , subfield(FieldName,'.',1)

                   

                  use the date # function

                   

                  Date#(right(fieldname,7),'MM.YYYY')

                  • Re: Date
                    Erdal Cücen

                    Hi,

                     

                    it was confused, but it works with:

                     

                     

                    Date#(left(right(DATAEGV_PERIODE,7),2),'MM') as Month,
                    Date#(right(DATAEGV_PERIODE,4),'YYYY') asYear

                     

                    Thanks alot.

                     

                    Regards,

                    Erdal

                    • Re: Date
                      Avinash R

                      try like

                      For 001 & 2016 fromat:

                      LOAD

                      subfield(DATAEGV_PERIODE,'.',1) as Month,

                      subfield(DATAEGV_PERIODE,'.',2) as Year

                      from

                      table ;

                       

                      For 01 & 2016 fromat:

                      LOAD

                      right(subfield(DATAEGV_PERIODE,'.',1),2) as Month,

                      subfield(DATAEGV_PERIODE,'.',2) as Year

                      from

                      table ;

                      • Re: Date
                        jagan mohan rao appala

                        Hi,

                         

                        You can also try like this

                         

                        Load

                        MakeDate(SubField(DATAEGV_PERIODE, '.', -1), SubField(DATAEGV_PERIODE, '.', 1) AS Date,

                        *

                        Regards,

                        jagan.

                        • Re: Date
                          Lokeshwari N

                          Hi Erdal,

                           

                          Try this,

                          Month(Date(Date#(DATAEGV_PERIODE,'0MM.YYYY'),'MM/DD/YYYY')) as Month,

                          Year(Date(Date#(DATAEGV_PERIODE,'0MM.YYYY'),'MM/DD/YYYY')) as Year

                           

                           

                          Regards

                            • Re: Date
                              Erdal Cücen

                              Hi,

                               

                              many thaks,

                               

                              i have solved with:

                               

                               

                              Date#(left(right(DATAEGV_PERIODE,7),2),'MM') as Monat,

                               

                               

                              Date#(right(DATAEGV_PERIODE,4),'YYYY') as Jahr

                               

                               

                              Regards,

                              Erdal

                               

                               

                              Von: Lokeshwari N

                              Gesendet: Donnerstag, 11. Februar 2016 09:45

                              An: Cücen, Erdal, NMM-FF <Erdal.Cuecen@S4M.COM>

                              Betreff: Re:  - Date

                               

                               

                               

                               

                                • Re: Date
                                  Stefan Wühl

                                  Erdal Cücen wrote:

                                   

                                  Hi,

                                   

                                  many thaks,

                                   

                                  i have solved with:

                                   

                                   

                                  Date#(left(right(DATAEGV_PERIODE,7),2),'MM') as Monat,

                                   

                                   

                                  Date#(right(DATAEGV_PERIODE,4),'YYYY') as Jahr

                                   

                                   

                                   

                                   

                                  If you do it like this, you won't get your year and month parsed in correctly (i.e. you will get values with an underlying numeric value based on a date, not on the commonly used month numbers 1-12 or years.

                                   

                                  Maybe you won't notice any issues untill you try to make e.g. selections or numeric comparisons.

                                   

                                  If you don't know what I am talking about, create a list box for Jahr field with an Expression

                                  =num(Jahr)

                              • Re: Date
                                Anshu Dwivedi

                                 

                                LOAD
                                  (
                                mid(DATAEGV_PERIODE,5,4)) as Year
                                ,
                                 
                                  (
                                mid(DATAEGV_PERIODE,1,3)) as Month

                                 
                                FROM
                                Table