topic Re: Max modified Date in QlikView

Max modified Date

Fri, 24 Jun 2011 21:25:50 GMT

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

johnw — Fri, 24 Jun 2011 23:24:31 GMT

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
;