6 Replies Latest reply: Feb 5, 2010 7:29 PM by Nimish Shah RSS

    Applymap for Dates problem

      Hi,

      I am trying o use applymap for date values and am having trouble getting the correct values. I am generating a date table with correspoinding date numbers ( as in 0 for the first date to n for the last date). This is my mapping table. In my other table I am trying to pull the dateno from the mapping table based on another date field. My script is as follows:

       


      Let vMinDate = num(Date#('01/01/2009','DD/MM/YYYY'));
      Let vMaxDate = num(Date#('31/12/2009','DD/MM/YYYY'));
      mHolidays:
      mapping LOAD Date(Date,'DD/MM/YYYY') as Date,1
      FROM
      Holidaylist.xls
      (biff, embedded labels, table is holidays$);
      Datestmp:
      Load Date($(vMinDate)+rowno()-1) as Date
      autogenerate($(vMaxDate)-$(vMinDate)+1);
      Dates:
      mapping Load Date,
      rangesum(peek('DateNo'),if(match(num(Weekday(Date)),6)=0,if(applymap('mHolidays',Date)=1,0,1),0)) as DateNo
      resident Datestmp;
      drop table Datestmp;
      tbluser:
      LOAD sdat,
      applymap('Dates',sdat) as sdatno,
      user
      FROM
      test.xls
      (biff, embedded labels, table is Sheet1$);


      The problem is that the value I am getting is 1 for all the fields instead of their dateno. Is this something to do with date formats? My date format is DD/MM/YYYY and if I write an inline table with date and dateno (not autogenerated), it seems to work. Can someone help me out here. I am attaching a sample file.

      Nimish

        • Applymap for Dates problem

          Hi,

          Can someone please help with this? I have also tried using map.. using. Bu the same results.

          Nimish

            • Applymap for Dates problem
              Peter Rieper

              Can you explain, what the purpose is in the calculation?
              So far you have managed to replace certain dates with a "1", which is most likely not wanted.

              Peter

                • Applymap for Dates problem

                  Hi Peter,

                  Thanks for responding. The real scenario is that I have a database of jobs done with various dates for each job eg. job uploaded on, data preperation completed on, coding completed on, reveiw completed on, job submitted on. Need to find out the turn aournd time at each stage. Each job is a single record with all these dates. Also the turn aournd is based on the number of actual working days. One way was to have a formula which counts the working days. I have that working. But by this method, I am creating datenumbers as suggested by one of the members (Morgan) - http://community.qlik.com/forums/p/24482/93503.aspx#93503

                  The advantage is that the calculatin for Turn around time will become simple in terms of subtracting the datenumbers.

                  This is the reason, I am trying to map the date numbers from a mapping table of dates and date numbers.

                  I hope I have been able to explain.

                  I want to know whether I am using the applymap correctly. If yes, why would it not work.

                  Nimish

                    • Applymap for Dates problem
                      Peter Rieper

                      Have not worked that much with apply-map, but think that expression are evaluated only after the running of the script. Have you tried other solution? My first approach would be to load the holidays and then to evaluate the dates with EXISTS-function, like IF(EXISTS(MyDate), 0, 1).*

                       

                      * actually you may use just the EXISTS(MyDate) and end with either 0 or -1 as result.

                      Peter