Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I'm a Qlikview newbie and have spent the morning trying to achieve something that I find fairly straightforward in MS Access. A bit of a learning exercise if you like. Here's what I'm trying to do...
I have a table of assets, each with a DateAcquired and DateReleased as below (DateReleased is null where we still have the asset)...
AssetID DateAcquired DateReleased
12345 01/09/2012 25/09/2012
12346 10/09/2012 -
12347 12/09/2012 01/10/2012
12348 13/09/2012 -
From this I need to derive a weekly volume of assets held based on the two date fields.
Now in MS Access I would first create a table of week numbers with start and end dates like this...
Week WeekStart WeekEnd
37 10/09/2012 16/09/2012
38 17/09/2012 23/09/2012
39 24/09/2012 30/09/2012
40 01/10/2012 07/10/2012
...then create a query with both the asset table and week numbers table (but without a join). I can then get a count of assets grouped by week number by adding the following criteria: DateAcquired<=WeekStart, DateReleased>WeekEnd OR IS NULL.
So far in Qlikview I've loaded my asset table and created a calendar table like the one above. I've tried using set analysis with similar critera to my Access query but I suspect I'm barking up the wrong tree with that. Basically I've reached the limits of my understanding and hope that someone out there can set me on the right track.
Many Thanks,
John
Hi John,
You might want to look into IntervalMatch() in the QlikView help or on the forums.
If you list all dates and weeks in your calendar you should be able to synch these numbers within the ranges of DateAcquired and DateReleased for all your AssetIDs.
Hi John,
You might want to look into IntervalMatch() in the QlikView help or on the forums.
If you list all dates and weeks in your calendar you should be able to synch these numbers within the ranges of DateAcquired and DateReleased for all your AssetIDs.
Echoing what Johannes suggested, there is similar example in the QV Cookbook. Can't recall the exact name, but I think it has "intervalmatch" in the description. It uses intervalmatch to count days for a piece of rental equipment. You could modify it to match weeks instead. Or match on date and count on distinct week to get a count.
-Rob
Guys, many thanks for your help. I've posted my code below should it be of benefit to anyone else. It works fine but I suspect my approach to arriving at a count by week is a little long winded. Maybe there's a smarter way of using IntervalMatch?
Cheers,
John.
Let varMinDate = Num('01/01/2012');
Let varMaxDate = Num(Weekstart(Today())-1);
Calendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as CalendarDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
SET NullValue = '31/12/2199'; //replace the null ReleaseDates with this date
NULLASVALUE ReleaseDate; //switch this on
Assets:
LOAD Num(AssetID) as AssetID,
Date(DateAcquired) AS DateAcquired,
Date(ReleaseDate) AS ReleaseDate,
1 as AssetCount;
SQL SELECT AssetID,
DateAcquired,
ReleaseDate
FROM tblAsset
WHERE AssetGroupID = 1
AND AssetTypeID IN(3)
AND Deleted = False;
NULLASNULL; //Switch NullAsValue off
ApplyIntervalMatch:
IntervalMatch (CalendarDate) LEFT JOIN LOAD DISTINCT DateAcquired, ReleaseDate Resident Assets;
LEFT JOIN (Calendar) LOAD * Resident Assets;
DROP Table Calendar;
//Get count of assets by week
AssetCountDaily:
Load CalendarDate,
Sum(AssetCount) as AssetCount
Resident Assets
Group By CalendarDate;
AssetCountWeekly:
Load Distinct Week(CalendarDate) as WeekNum,
Min(AssetCount) as AssetCount // I only want assets that are with us for the entire week
Resident AssetCountDaily
Group By Week(CalendarDate);
DROP Tables Assets, AssetCountDaily;