topic Re: master calendar script within a table load? in QlikView

master calendar script within a table load?

stevelord — Tue, 17 May 2016 16:01:54 GMT

Hi Community, I'm generally familiar with master calendars and have plenty of ideas to experiment with, but posting here in case anyone has a ready solution and I spend my day hitting dead-ends.

So we have a regular master calendar setup to follow calendar years, but now I need customized calendars that track with 4- and 6- week activities that various clients have. The database table I have for these activities is like this:

ActivityID StartDate EndDate

123 1/1/2016 1/28/2016

456 2/1/2016 2/28/2016

Specifically, I need activity calendar weeks that flow from the various StartDates, so when the end user selects challenge 123, the Challenge Weeks go 1 to 4 in 7-day increments from 1/1/2016. And if they select challenge 456, the challenge weeks start week 1 on 2/1/2016.

Things I ruled out:

Expression in the table didn't like me inserting min(MCDate) stuff into the if statements.

Calculated dimension laughed at my dual if statement and gave a //error in calculation

I have a master calendar with all the days populated, and did like:

sum(if(MCDate>=min(MCDate) AND MCDate<=(min(MCDate)+6), Minutes)) for the expression that failed.

I pushed the date range definitions out to a dual if statement in the calculated dimension, and cut the expression down to just sum(Minutes), but the calculated dimension didn't work either.

So I'm looking back at the script to see if I can create a ChallengeWeeks field there to use as a dimension on the front, and sum(Minutes) on that field in the front. I'll be playing with that now, and look forward to any input from anyone else who has had the same kind of requirements. Thanks!

***Edit: It was min(MCDate) I was trying to work with initially and user would select the challenge dates on the master calendar. I'm just now bringing in the actual challenge dates in script to see if I can make smarter expressions. I might actually be okay doing the expression with StartDate and StartDate+6 once I have it in actually, since no min() to confuse it.

Edit2: And making the challengeweeks field in script is probably the optimal solution so the tables on the front don't have to handle those computations every time a selection is made. I will do that if I see how.

Re: master calendar script within a table load?

Anonymous — Tue, 17 May 2016 16:10:00 GMT

Hi Steve

I may be wrong here but would as of table not help you with this scenario?

Re: master calendar script within a table load?

stevelord — Wed, 18 May 2016 18:47:33 GMT

Thanks Brian, I googled that and found a nice detailed training/blog about 'as of' tables here:

The As-Of Table

It looks like it would work beautifully with a single calendar, but I'm not sure how it will cope with the fairly random start/end date periods of the various client activities that are happening. For instance, one client's 'Week 1' might be Jan1 - Jan 7, and another client's 'Week 1' might be.. April 15 - April 21.

What I've done instead is load the table with all the client activity start/end dates:

Client Activity StartDate EndDate

A run 1/1/2016 1/28/2016

B walk 2/1/2016 2/28/2016

There is also a table listing which people belong to which client,

and another table listing the people, how much they did, and when they did it (activity date)

the people's activity dates are connected to a master calendar so we can see days of no activity too

On the front I have a straight table like this:

Dimensions: Client, Activity, StartDate, EndDate

Expressions:

1. Sum minutes people logged in 'week 1' of their respective activity:

=sum(if([ActivityDate]>=StartDate AND [ActivityDate]<=StartDate+6 AND [ActivityDate]<=EndDate, [Activity Minutes]))

2-6. and five more expressions like that adjusted to cover the 6 weeks each activity period can last.

Since some only go 4 weeks, I put conditional shows on the columns and the <=EndDate in the expression to show only columns with data and data during the activity period.

My approach works nicely with a few small fake test spreadsheets. Will see how it goes when I plug it into a database with millions of rows for qlikview to run computations on.

I will say that the 'as of' table approach's expressions looked slightly less demanding on the front end, aside from the fact only 1 might be needed. But it also looks like it needs a pivot table and pivot tables always seemed a tad more demanding than straight tables- especially when I start chuckng in percents next to sums or the like- pivot tables get tricky fast when you want to add more expressions where straight tables let you do whatever you want. Sense of pivot tables being more demanding on resources could be my imagination though- I know the act of pivoting a column is demanding, but maybe it's no different from straight tables once pivoted and locked in. 'As of' table is probably a lot friendlier with more complex expressions though since the weeks/months are defined in a field, and I like the idea of just pivoting 1 expression vs copy/pasting practically the same one many times, so I will definitely keep it in mind if I hit a wall with my current approach.

For now, I'm not sure how it behaves with the multiple/random date ranges I'm working with on one table, and I have something I'm sure of that's okay for these 4-6 week activity periods.

PS> Apologies for poor organization and any other editting issues, just typing fast and getting back to work now. I tried to make this helpful for posterity since there are advantages and drawbacks to either approach.

Re: master calendar script within a table load?

Anonymous — Thu, 19 May 2016 12:28:45 GMT

Thanks for your explanation Steve and I hope your solution works in your full application. It sounds like a very complex scenario.

Good luck

Brian

Re: master calendar script within a table load?

stevelord — Thu, 19 May 2016 13:51:00 GMT

Yeah, In my question I was initially envisioning a way to use master calendar script inside the load of the table itself, to create a Weeks field populates values based on the startdate values and some math for each activity.

Something like

LOAD ClientActivityInfo

Client,

ActivityName,

StartDate,

EndDate

From databasetable;

Join load... some master calendar script and computations defining Weeks using StartDate as vMinDate and EndDate as vMaxDate <--If I have time or my other solution doesn't work, I'd probably spend a few hours trial/erroring my way through this and maybe 60% chance of getting nowhere on it until I know more next year

Store into qvd, drop table, and reload single table next.

Final table in data model would look like

Client ActivityName StartDate EndDate CalendarDate Week

A run 1/1/16 1/28/16 1/1/16 1

A run 1/1/16 1/28/16 1/2/16 1

A run 1/1/16 1/28/16 1/3/16 1

....

A run 1/1/16 1/28/16 1/28/16 4

B walk 2/1/16 2/28/16 2/1/16 1

........

And the pivot table on the front would look like

Dimensions: Client, ActivityName, Week - and Week pivoted to horizontal

Expression: Sum(Activity Minutes

Client ActivityName Week 1 2 3 4

A walk 10 20 35 50

B run 15 60 45 0