2 Replies Latest reply: Sep 13, 2011 5:44 AM by Nenad Vukovic RSS

    Issue with ApplyMap and one Date

      Hi all,

       

      An example which is giving me a hard time:

      1. Periods are set as: (FYI: 01.01.1900 denotes a opening balance in an ERP):

           Comp, Date, FPeriod

           01, 01.01.1900, 00-2011

           01, 01.01.2011, 01-2011

           ...

      2. When I applymap as follows: applymap('FiscalCalendar', '$(vComp)-'&date(DateFromSQL)) as FPeriod, I don't get correct value for 01.01.1900 as 00-2011 but:

       

      I don't see a difference between the two as data are there.

       

      Thanks in advance for answers/suggestions/comments

        • Issue with ApplyMap and one Date

          Hi,

           

          Can't see the image you've pasted ... but there's a chance that the below might work.  I'm not sure it would be happy with the hyphen next to the variable in your original  '$(vComp)-'&date.

           

          applymap('FiscalCalendar', '$(vComp)'&'-'&date(DateFromSQL)) as FPeriod

           

          Might not work, but worth a try unless you already have :-)

           

          Cheers,

          Emma

            • Issue with ApplyMap and one Date

              Thanks Emma,

               

              It is the same

               

              The screen shots shows this:

              01.01.1900  01-01.01.1900      -> not expected

              01.01.2011  01-2011               -> expected

               

              It looks like there is an issue with interpreting dates between Excel and QV. For example when I create a table I see

              31.12.1899 in QV for 01.01.1900 in Excel.

               

              Then I got an idea to change 01.01.2900 to 02.01.1900 and now it works fine

               

              Thx