1 Reply Latest reply: Jun 24, 2011 7:24 PM by John Witherspoon RSS

    Max modified Date

      Hi All,

      How do I find the max modified date while loading data from a qvd. I want it for the load script.

      Once I find the max modified date I also need to do a bucket analysis. Anything older than 3 months, 6 months and so on.

      That means Max(Modified Date)-Modified Date>90 is '>3 Months' and  Max(Modified Date)-Modified Date>180 is '>6 Months'.

       

      Any help will be greatly appreciated,

      Thanks,

      Bikash

        • Re: Max modified Date
          John Witherspoon

          Something like this perhaps.

           

          Dates:
          LOAD date(fieldvalue('modified date',recno())) as Date
          AUTOGENERATE fieldvaluecount('modified date')
          ;
          MaxDate:
          LOAD max(Date) as MaxDate
          RESIDENT Dates
          ;
          LET vMaxDate = num(peek('MaxDate',0,MaxDate))
          ;
          DROP TABLES Dates, MaxDate
          ;
          LEFT JOIN (YourTable)
          LOAD
          your key field or fields
          ,if("modified date"<addmonths($(vMaxDate),-6),'>6 Months'
          ,if("modified date"<addmonths($(vMaxDate),-3),'>3 Months')) as Bucket
          RESIDENT YourTable
          ;