4 Replies Latest reply: Dec 1, 2011 8:49 PM by Hairul Annuar Mohd Khaliluddin RSS

    Unable to convert date in date format

    Hairul Annuar Mohd Khaliluddin

      Dear All,

       

      I facing difficulties to convert date from Date column to TranxDate column retrieve from crosstable. 

      I had tried use Date# and MakeDate statement but can't resolve. 

      In this discussion together I'm attach the file image, script and excel file.

       

      Please advise.

       

       

      1.jpg

       

      Scripting

       

       

      Date:

      LOAD @1,

           @2,

           @3,

           @4,

           Date(@5) as @5,

           Date(@6) as @6,

           Date(@7) as @7,

           Date(@8) as @8,

           Date(@9) as @9,

           Date(@10) as @10,

           Date(@11) as @11,

           Date(@12) as @12,

           Date(@13) as @13,

           Date(@14) as @14,

           Date(@15) as @15,

           Date(@16) as @16

       

      FROM

      [..\..\..\QlikViewSupplyDemandSharedFolder\Yearly\HolticultureBudget\VBU2011.xlsx]

      (ooxml, no labels, header is 7 lines, table is [Produce & Variable Cost], filters(

      Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

      ))

      Where @2='SKU Code';

       

       

       

       

       

       

      //To get the excel file date

                Let Date1 = peek('@5',0,num#('Date'));

                Let Date2 = peek('@6',0,num#('Date'));

                Let Date3 = peek('@7',0,num#('Date'));

                Let Date4 = peek('@8',0,num#('Date'));

                Let Date5 = peek('@9',0,num#('Date'));

                Let Date6 = peek('@10',0,num#('Date'));

                Let Date7 = peek('@11',0,num#('Date'));

                Let Date8 = peek('@12',0,num#('Date'));

                Let Date9 = peek('@13',0,num#('Date'));

                Let Date10 = peek('@14',0,num#('Date'));

                Let Date11 = peek('@15',0,num#('Date'));

                Let Date12 = peek('@16',0,num#('Date'));

      //          Let Date12 = peek('@16',0,'Date');

       

       

       

       

       

       

      TempBudgetReset:

      LOAD

           @2 as Item_code,

           if(isnum(@5)=-1,@5,null()) as '$(Date1)',

           if(isnum(@6)=-1,@6,null()) as '$(Date2)',

           if(isnum(@7)=-1,@7,null()) as '$(Date3)',

           if(isnum(@8)=-1,@8,null()) as '$(Date4)',

           if(isnum(@9)=-1,@9,null()) as '$(Date5)',

           if(isnum(@10)=-1,@10,null()) as '$(Date6)',

           if(isnum(@11)=-1,@11,null()) as '$(Date7)',

           if(isnum(@12)=-1,@12,null()) as '$(Date8)',

           if(isnum(@13)=-1,@13,null()) as '$(Date9)',

           if(isnum(@14)=-1,@14,null()) as '$(Date10)',

           if(isnum(@15)=-1,@15,null()) as '$(Date11)',

                 if(isnum(@16)=-1,@16,null()) as '$(Date12)'

       

       

       

      FROM

      [..\..\..\QlikViewSupplyDemandSharedFolder\Yearly\HolticultureBudget\VBU2011.xlsx]

      (ooxml, no labels, header is 8 lines, table is [Produce & Variable Cost], filters(

      Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

      ));

       

       

       

       

       

       

      TempCross:

                CrossTable(Date,BudgetQty,1) //change the field name

                Load

                          *

                          Resident TempBudgetReset;

       

       

       

       

       

       

      BudgetReset:

                Load

                          *,

                          'Budget' as Flag,

                          Makedate(Left(Date,4),Mid(Date,5,2),Right(Date,2)),'MM/DD/YYYY' as TranxDate

                          Resident TempCross;

       

       

       

       

      Drop Table Date;

      Drop Table TempBudgetReset;

        • Unable to convert date in date format
          Stefan Wühl

          I believe your Date is only a string, not a recognized date data type with numerical representation.

           

          You should use something like

          date#(@5,'M/D/YYYY') as @5

          in first load to parse the date in (date() will only format the value, you need date#() to parse it in, check out also the format codes).

           

          Hope this helps,

          Stefan

            • Re: Unable to convert date in date format
              Hairul Annuar Mohd Khaliluddin

              It is imposibble to do that,

               

              If I do embedded label for the date row follow with date#(@5,'M/D/YYYY') as @5 as your suggestion it will shown empty value, while the date is not embedded the qty value in column @5 qty will be change to date

               

              Together is this discussion I have attached my source file for better understanding.

               

               

               

              Requisitioner assistance,

              Hairul

                • Re: Unable to convert date in date format
                  Stefan Wühl

                  Hairul,

                   

                  I think it could work like attached modified script. The important change seems to be

                   

                  Date(Date#(Date),'MM/DD/YYYY') as TranxDate

                   

                  I believe the dates are read in correctly as date type, but somewhere on their way they are transformed to strings again (because of the field value  to variable to field name to crosstable loaded field value transformations). Since we format the dates using date() function on the very first load, a plain date#() on the last load should be enough. See attached sample file.

                   

                  As a side note, peek() function takes the table name as third parameter, so I don't think a num#('Date') is useful here, and I removed the num#() function.

                   

                  Date:

                  LOAD @1,

                       @2,

                       @3,

                       @4,

                       Date(@5) as @5,

                       Date(@6) as @6,

                       Date(@7) as @7,

                       Date(@8) as @8,

                       Date(@9) as @9,

                       Date(@10) as @10,

                       Date(@11) as @11,

                       Date(@12) as @12,

                       Date(@13) as @13,

                       Date(@14) as @14,

                       Date(@15) as @15,

                       Date(@16) as @16

                  FROM

                  [.\VBU2011.xlsx]

                  (ooxml, no labels, header is 7 lines, table is [Produce & Variable Cost], filters(

                  Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

                  ))

                  Where @2='SKU Code';

                   

                   

                  //To get the excel file date

                            Let Date1 = peek('@5',0,'Date');

                            Let Date2 = peek('@6',0,'Date');

                            Let Date3 = peek('@7',0,'Date');

                            Let Date4 = peek('@8',0,'Date');

                            Let Date5 = peek('@9',0,'Date');

                            Let Date6 = peek('@10',0,'Date');

                            Let Date7 = peek('@11',0,'Date');

                            Let Date8 = peek('@12',0,'Date');

                            Let Date9 = peek('@13',0,'Date');

                            Let Date10 = peek('@14',0,'Date');

                            Let Date11 = peek('@15',0,'Date');

                            Let Date12 = peek('@16',0,'Date');

                  //          Let Date12 = peek('@16',0,'Date');

                   

                  TempBudgetReset:

                  LOAD

                       @2 as Item_code,

                       if(isnum(@5)=-1,@5,null()) as '$(Date1)',

                       if(isnum(@6)=-1,@6,null()) as '$(Date2)',

                       if(isnum(@7)=-1,@7,null()) as '$(Date3)',

                       if(isnum(@8)=-1,@8,null()) as '$(Date4)',

                       if(isnum(@9)=-1,@9,null()) as '$(Date5)',

                       if(isnum(@10)=-1,@10,null()) as '$(Date6)',

                       if(isnum(@11)=-1,@11,null()) as '$(Date7)',

                       if(isnum(@12)=-1,@12,null()) as '$(Date8)',

                       if(isnum(@13)=-1,@13,null()) as '$(Date9)',

                       if(isnum(@14)=-1,@14,null()) as '$(Date10)',

                       if(isnum(@15)=-1,@15,null()) as '$(Date11)',

                       if(isnum(@16)=-1,@16,null()) as '$(Date12)'

                   

                   

                  FROM

                  [.\VBU2011.xlsx]

                  (ooxml, no labels, header is 8 lines, table is [Produce & Variable Cost], filters(

                  Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

                  ));

                   

                  TempCross:

                            CrossTable(Date,BudgetQty,1) //change the field name

                            Load

                                      *

                                      Resident TempBudgetReset;

                   

                   

                  BudgetReset:

                            Load

                                      *,

                                      'Budget' as Flag,

                                      Date(Date#(Date),'MM/DD/YYYY') as TranxDate

                                      Resident TempCross;

                   

                  Drop Table Date;

                  Drop Table TempBudgetReset;