30 Replies Latest reply: Aug 1, 2012 7:06 AM by Anosh Nathaniel RSS

    Best Practices Re: a Calendar Table and Multiple Date Fields

    Jonathan Shaltz

      Howdy.  I've searched through the forum, but not found a post which quite addresses this question.  This one was close, but represented the opposite problem.  The "similar posts" tool found more results han my searching: this post, this one, and this one cover some solutions, but don't discuss which is best, or more generally what advantages and disadvantages exist.  What's easiest?  What's most commonly used?  Has anyone used one of these solutions for 12 months or so and has some feedback on how well it's worked?

       

       

      A common practice in data warehouses is to have a Dates table which gives values such as year, quarter, and month for each day.  It looks like this is common in QlikView too, and the name "calendar table" seems to be widely-used.  If this calendar table has a Date field as its key, any queries which also contain a Date field will automagically join to it.  Groovy!

       

      But... what do you do when you have multiple fact tables, with different date fields, which should not join together?  For example, say there's a Customers.Date field representing the date the customer was created, and an Orders.Date field representing the date an order was placed.  A report which contains both fields will need to rename at least one of them, breaking the join.

       

      Also, a fact table may contain multiple date dimensions.  In my case, the Orders table has OrderDate, DatePaid, and ShipDate fields.  I could create three more calendar tables, with key fields of OrderDate, DatePaid, and ShipDate, respectively.  This seems cumbersome; is this the best practice, or is there a simpler option?

       

      In SQL, I'd write three JOINs, to the same table each time but specifying a different field from the first table each time.  But QlikView seems to be restricted to natural joins, which as a DBA makes me wince.

       

      Instead of using one or more calendar tables, you could just import the date values as dates, and use QlikView date functions to populate your date hierarchies.  Is this what most users end up using?

       

      I appreciate any input.  Thank you!

        • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
          John Witherspoon

          I think your third link above covers the two basic options - create a separate calendar for each field, or create a new table that links dates by date type to a single calendar.  It also discusses the advantages and disadvantages of each approach, though mostly in "how the users will interact with it" terms, which in my opinion is primary.

           

          In your case, it sounds like you want to keep all these dates separate.  You would never want to select November 28, 2011 in a single Date field, and see orders that were ordered, paid OR shipped on that date.  If you want to see orders ordered on that date, you would explicitly select that OrderDate.  That's option 1 in the third linked thread, which is also the easiest and clearest for most developers, I'd guess.

           

          I do consider a separate calendar table for each distinct kind of date to be the best practice when you want your dates to be distinct.  However, there's no real disadvantage to handling it with SQL-like joins.  Denormalization isn't really a problem in QlikView, and any extra memory it might have required will be recovered by compression.  If anything, the user interface may actually work marginally faster.  We tend to not do this, though, perhaps because the script may be a little slower, or maybe just because everyone else seems to do it with a separate calendar, so that's just what everyone learns, even if there's no real reason for it.

           

          What I've personally done, though I'm not sure that this is common, is create a Calendar.qvd.  This helps me enforce standards and centralizes logic if we, say, decide to change our definition of week numbers.  If you wished, your example could work with a resident calendar, or you could generate a new calendar for every field.  Anyway, the script for the SQL-like join approach would be something like this:

           

          LEFT JOIN (Orders)
          LOAD
          Date as OrderDate
          ,Month as OrderMonth
          ,Year as OrderYear
          FROM Calendar.qvd (QVD)
          ;
          LEFT JOIN (Orders)
          LOAD
          Date as DatePaid
          ,Month as MonthPaid
          ,Year as YearPaid
          FROM Calendar.qvd (QVD)
          ;

           

          And so on for every date field in every table.  I can't think of anything wrong with that if you prefer the explicit joins instead of QlikView's association.  I suspect it is more common to have separate tables, but there's really no need.  The same is true even if you just have a single date field - it may be most common for us to create a calendar table, but it isn't necessary, and there's no real reason NOT to just join the data.

            • Best Practices Re: a Calendar Table and Multiple Date Fields
              Jonathan Shaltz

              Correct; I'd like users to be able to filter on each date field separate.  Orders placed in September, paid in October, and cancelled in November, for example.

               

              I have a Dates QVD, so your rename-and-join method works, which would save me from over-normalizing my fact tables.  What makes me hesitate it that I'd have to write dozens of lines of aliases, one for each of the 22 fields in the date table x the number of date fields in my fact table.  That's well over 100 lines, to be repeated in almost all reports.  Thousands of lines of code, just to stop the system from joining!  And if someday a date field is renamed or deleted, I'd need to change the script for every report, making one change for each date field, so several hundred manual changes.  This wouldn't be so bad if one could edit QlikView scripts as plain text, separately from the associated reports.

               

              One could save typing by QUALIFYing each copy of the dates table, but then the fields would have ugly names like "Order Date.Is year-to-date".  Since field names are the primary interface for non-technical users, I'd like to keep them "pretty".

               

              Using multiple QVDs, one for each date field in the warehouse, would still require hundreds of aliases, but at least I would not have to repeat them in each report.  It bugs me to store the same data a dozen times over, though it's a small table.

               

              These all seem like hacks, painful to build and fragile in use.  Are there other approaches?  Is there's no way to specify the field(s) by which tables are to be joined, other than aliasing every field?

                • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                  John Witherspoon

                  This seems a fundamental limitation of what you want your users to see, not a QlikView limitation.  Note that if you were doing this purely with SQL, you can join any way you want, but if you want your users to see a field DatePaid, either you have a table with DatePaid on it, or somewhere in your SQL you're going to see Date as DatePaid.

                   

                  As far as a better way to do all that aliasing, I'm running off to a meeting, but I think you could use qualify initially, then use a loop through the fields, renaming them according to your pattern.  I'm sure we could come in well under 100 lines if you were doing a load *.  Though I'd personally avoid load * because it opens you up to unintended associations if people add new fields to the calendar QVD.

                    • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                      Jonathan Shaltz

                      Well, the big difference in SQL is that I'd only put aliases on the fields I'm using, so three or four per report, not dozens.

                       

                      Hmm, so one can rename specific fields after the load?  That would be something, I could live with ugly "Order Date"."Is year-to-date" fields if it meant I just needed to alias a few fields in each report.  Something like this?

                       

                       

                      Orders:
                      LOAD * FROM Orders.qvd (QVD);
                      
                      OrderDate:
                      QUALIFY *;
                      ALIAS OrderDate.%DateKey AS %OrderDateKey;
                      LOAD * FROM Dates.qvd (QVD);
                      

                       

                       

                      It's a bit ugly (the resulting QVW, that is; the script is nice and consise), but it works.  I am torn as to whether to accept this or to create the dozen separate QVDs after all.

                        • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                          John Witherspoon

                          So only rename the fields you're using.  Why would you load and force yourself to rename fields you don't need?  As I suggested earlier, LOAD * FROM Dates.qvd (QVD); is a dangerous practice that leaves you open to unintended associations as people add new fields to the QVD.  It also wastes memory to load fields you don't need.  Same for ANY load, really.  Avoid LOAD * except from something like an inline table where you have full and obvious control over what's being loaded.

                           

                          Following up on what I mentioned in my previous post, I've come up with the attached example that automatically creates calendars associated with every date field in every table.  Again, I wouldn't do the load * like in this example, but you could.  I suspect it also has a bug if you have the same date field in two different tables already, as it will probably try to load the same calendar and field names twice.  I'm sure it's a solvable problem, but I wouldn't actually use this code anyway, so it's just an example of what's possible.  As an example, I think it's good enough.  In practice, what I do is load the specific fields I need, no more, no less, and rename as appropriate in the load.

                           

                          Edit: I suspect the script can be simplified.

                            • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                              Jonathan Shaltz

                              It's necessary to rename all the fields for two reasons.  Because I'm producing raw data which other people may use, I can't be sure about which fields they may want.  Also, if I leave any field names as-is, QlikView will join my date tables together (sigh).

                               

                              I'm OK with "LOAD *" (flashback to the Commodore 64 magazine), because I have control over the QVDs and the SQL views from which they get their data.  No one else will be changing these files.  Plus it's tedious typing every field thrice over (once in the SQL field list, and twice in the LOAD list).  The QVDs need all fields, per se; they must store everything so that actual QVWs have access to everything.

                               

                              Thanks for the sample file.  So if I used lower-case names in the Dates.qvd file, and "Order date" as the field name in the orders table, I'd end up with "Order date year", "Order date month", "Order date is business day", "Order date is year-to-date", etc.  Nice!  That's a little prettier than QUALIFY, which gives me "Order date.Year", "Order date.Month", etc.  The downside is 20 lines of code which would need to appear in every script, but there should be no reason for the script to change if, say, we add or rename a field, so that's probably not a time bomb.

                               

                              I'm still curious about what other approaches people have used, and among the two talked about in this thread and the linked threads, which is more popular and why.  Are there long-term gotchas with either approach?

                                • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                                  John Witherspoon

                                  You can make arbitrarily-complicated logic to recognize your date fields, probably up to and including regular expressions, though I'd have to refresh my memory on how that is done.  This was just an example, and an example that assumed your naming convention for date fields was '* Date'.  If it's '* date', then use ' date' where I used ' Date'.  Alternatively, just use a "for each in" loop, and specify the date fields you want to build calendars for.  The example was trying to go all the way, where merely adding a date field to some QVD the program knows nothing about would result in a calendar being loaded for that date field.  But following your earlier argument, you have control over the QVDs, so when you add a date field, you can know to put it in the list.  A combination might also work, where anything following the standard pattern would be picked up automatically, while anything that didn't could be placed in an explicit list.

                                   

                                  As far as which of the two main approaches is more popular, I don't see how popularity has anything to do with it because they aren't two ways of solving the same problem.  They're solutions to two DIFFERENT problems.  Your users' requirements should determine which approach you use, or if you use both at once.  So let's say you have an application that displays order data.  This system has two dates, an order date and a ship date.

                                   

                                  • Do your users want to be able to select December 1, 2011, and see all orders placed OR shipped on that date?  Then create a single generic calendar linked by date type.
                                  • Do your users want to be able to see orders that were both placed on November 15, 2011 and then shipped on November 20, 2011?  Then create separate calendars.
                                  • Do your users want to be able to do either of these things?  Then create separate calendars AND a generic calendar.

                                   

                                  I'm going to guess that the separate calendars are more popular in the community by far.  I'm also going to guess that this has little to do with user requirements, and more to do with it being more easily understood by more developers, so that's just what they do.  If users ask for the other requirement, I expect many developers to get confused, or even say "QlikView can't do that", and leave it at that.  In my own applications, I suspect that it's around a 50/50 mix, and I think I only have one application with both approaches at the same time, and even there, one approach (generic calendar) is primary and obvious, and the other is secondary and somewhat hidden, supplied only for a single power user.  We've been using QlikView for 6 years using both approaches, and I don't think there have been any gotchas with either.  I'm sure there has been SOME confusion with both approaches, but none that ever made it back to me (and I get a lot of phone calls and emails from users).

                                   

                                  Edit: One of my applications has the generic calendar approach but can still answer the kind of questions that separate calendars answer.  It does this through an "add to memory" button.  So for the example you'd select the "order" date type, and select November 15, 2011.  Then hit "add to memory", and what it does is select all of the possible order IDs.  Then select the "shipped" date type, and select November 20, 2011.  Now you have the same orders as you would have had with separate calendars.  A user could of course do this manually without a button.  But either way, if your users really want to do that, I'd just provide separate calendars, since that makes it easier for them.

                                   

                                  Sorry, I don't mean to monopolize the replies when you're surely trying to get more of a community perspective instead of my personal perspective.  I should probably stop answering and give other people a chance to provide their own opinions.  And maybe people will be more likely to do that if I make it obvious like this...

                                   

                                  What are other people's thoughts?

                                    • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                                      Jonathan Shaltz

                                      Sure; I made a few tweaks to your code, mostly to clarify what was happening, but that's just detail.  This is exactly what I needed!

                                       

                                      I should have been more clear: the two approaches I was contrasting were (A) multiple date files or (B) joining to the same date file multiple times, using some mechanism to rename its fields for each join.  I see how the former approach, which does not require any scripting, would appeal to most users.

                                       

                                      Certainly what's most popular shouldn't be the only factor, or even primary, but if most people are doing one thing, it deserves consideration.  We've examined this from a couple of angles over the past few days, but there may be more approaches we haven't considered.  Though if there is such an animal, given six years with QlikView and 4,500 forum posts, you probably would've seen it by now.

                                        • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                                          John Witherspoon

                                          I think in your situation, with a whole lot of different date fields, and wanting all the calendar information for each, I'd left join all the calendar data back onto the main QVD multiple times.  I wouldn't want a separate calendar QVD for each date field - that's a lot of joins in the user application.  And I wouldn't want a single calendar QVD, because that's just as many joins PLUS a bunch of renames.  So I'm thinking you modify the loop to do the joins when you're creating the main QVD.

                                           

                                          I haven't thought about it long and hard, but that's certainly my initial impulse.

                                           

                                          In fact, it has me wondering if I should do something like that around here.  We DO have a lot of dates on the order item, but don't tend to use more than one or two in any given user application.  That tends to make the load from a generic calendar QVD pretty simple, and I didn't want to inflate the size of my QVDs, so the choice seemed clear at the time.  But maybe we shouldn't be doing the generic calendar load at all.  Maybe all the date fields should have all calendar fields associated with them by default in the order item QVD.  Maybe I've been doing it the "wrong" way all along.

                                           

                                          Hmmm.

                                           

                                          Edit: Oh, and I'd guess that neither of your approaches is the most popular.  I'd guess that the most popular approach is to just add additional fields in the user application during the QVD load, simple month(Date) as Month types of things, deoptimizing the load, or as a left join by key with probably similar performance penalties.  I'd bet that the second most popular approach would be to generate calendar tables after the main load in the user application, which can have high performance if done with fieldvalue().  A calendar QVD is probably at least third on the list.  Multiple calendar QVDs or building everything into the main QVD I'm guessing are rather rare.  So I'm thinking the best approach for you is probably one of the most rare things to see people actually do.

                                            • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                                              Jonathan Shaltz

                                              I'm not sure I'm following you.  If we're agreed that multiple Date.qvd files is not optimal for this scenario, then it follows that we'll have just one, right?  Are you suggesting that there be multiple QVDs, generated automatically with the various prefixes?  That would eliminate the need to have code for auto-generating Dates tables in QVW files, but would instead entail multiple joins, once for each date field used in the QVW.  My Dates.qvd file is 146 KB, for the 7,600-odd days from 2000/01/01 to 2020/12/31, so the space required for multiple files shouldn't have an impact, except for slow connections like VPNs or some home DSLs.

                                               

                                              Or are you suggesting putting all the date fields in each fact table's QVD?  That was the approach I started with, but it definitely bloats things to add 20 date fields for each of four date fields in a fact table x 10 million Orders rows.  Cutting this out has helped me a lot; my BI server is out-of-state and the bandwidth is not what it could be.  So that's a third option, though it entails larger fact tables.

                                               

                                              The script-based date table multiplication pattern which your code implements has the advantage of making all date fields available, all the time.  And it requires a minimal amount of disk space.  I'm having trouble with one line, but I'll post it here when I have it pinned down.

                                                • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                                                  John Witherspoon

                                                  Right, I was "suggesting putting all the date fields in each fact table's QVD".  All fields are then available in one QVD, and the user applications load the ones they need (or load * if you like).  I understand your point about QVD bloat, though, and it's certainly one reason I hesitate doing this myself.

                                                   

                                                  If you can nail down the script code, you can include it rather than cutting and pasting.  So then user applications would load all their data, and then just have a little include to build all the associated calendars.  But then the USER applications have the bloat, because that's almost certainly more fields than they really need.  It probably isn't that big a deal in practice, though.  How many rows X columns can you have from a few calendar tables?  Probably much less data there than your fact tables most of the time.

                                                    • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                                                      Jonathan Shaltz

                                                      Gotcha.

                                                       

                                                      Regarding the bloat, I'd argue that it goes the other way.  With the multiple-calendar-files approach or the one-file-loaded-multiple-times approach, each QVW loads the whole dates QVD once per date field among the tables loaded.  Assuming ten date keys, that's about 1.5 MB: 145 KB for Order Date, 145 KB for Date Paid, 145 KB for Ship Date, etc.  I'm using my Dates.qvd file for reference here; it works out to 19 bytes per daily record, 145 KB total / 7,761 days from 2000/01/01 to 2020/12/31.

                                                       

                                                      However, putting these date fields in the fact tables' QVDs means adding those 19 bytes once for each date field in the fact table, for each row in the fact table.  If our main fact table has six date keys and one million rows, that's 19 bytes x 6 x 1.0E6 = 109 MB.  And if we have ten million rows in our fact table, we'll hit the 1 GB mark.  I had to give up on this approach when I first took a stab at translating my data warehouse to QlikView, because the load times were just too painful.

                                                       

                                                      If your calendar file is narrower, this would be less of an issue.  However, most of my 22 date fields are Yes/No, which are probably being compressed to bits.  So even if you just have the usual suspects like Year, Month, Week, Day, and Day of week, I bet that would use around three-quarters as much space.

                                                       

                                                      If load times are the top concern, I guess you'd load a single integer date and use date functions to expand that in the QVW?  Your consumers would need to be comfortable with some fairly complex expressions.

                                                        • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                                                          Jonathan Shaltz

                                                          It's things like this that drive me crazy trying to work with QlikView.  I've spent hours going over my script, trying to see why it wasn't working, and then today I see a forum post.  It turns out that my script is correct, but there's a bug/undocumented limitation such that you're not allowed to rename a field once it's been used as a key (but you can't force the system to NOT use a field as key!).  I'd be OK with this if QlikView gave me a little message "you can't rename keys, bub," but instead it SILENTLY SWALLOWS THE ERROR!  No warning!  No message!  It just ignores the RENAME FIELD command.  What sadist thought that was a good idea?!

                                                            • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                                                              John Witherspoon

                                                              JonathanShaltz wrote:

                                                               

                                                              It's things like this that drive me crazy trying to work with QlikView.  I've spent hours going over my script, trying to see why it wasn't working, and then today I see a forum post.  It turns out that my script is correct, but there's a bug/undocumented limitation such that you're not allowed to rename a field once it's been used as a key (but you can't force the system to NOT use a field as key!).  I'd be OK with this if QlikView gave me a little message "you can't rename keys, bub," but instead it SILENTLY SWALLOWS THE ERROR!  No warning!  No message!  It just ignores the RENAME FIELD command.  What sadist thought that was a good idea?!

                                                               

                                                              I entirely agree with you.  First, it's ridiculous that we can't rename to a field that already exists.  I'm sure they're just trying to protect us from ourselves, but to me, it's an obvious and straightforward way to create associations.  Why shouldn't I be able to load a table, manipulate it all I want, and THEN rename a field to create an association?  Not allowed.  And second, yes, it's even more ridiculous that it returns no error, and simply ignores the rename.  QlikView's error reporting sometimes leaves a lot to be desired.  Third, I almost guarantee that if you sent this to QlikTech as a bug, they'd say "Working as Designed" and close your ticket with no further action.  You would have to submit it as a feature request.  I've pretty much stopped reporting bugs after getting that response a few times.  I like QlikView and QlikTech in general, but there are definite areas for improvement, areas for improvement that seem rather surprising to me for a mature product and mature company.

                                                              Anyway, that's one reason why my script was as tortured as it was - I had to make sure the date field itself got the right name up front rather than fixing it after the fact in the main rename loop.  I perhaps should have mentioned that, as fixing that "mistake" seems an obvious thing to do when presented with my script.

                                                                • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                                                                  Jonathan Shaltz

                                                                  OK, I'm glad to hear it's not just me.  Part of why it's frustrating is that there's so much good stuff in QlikView, if they'd smooth out some of these rough spots (instead of chasing fads with mobile BI, hmm?) they could have such a kick-ass product.

                                                                   

                                                                  Here's what worked.  I'd thought I could simply rename the key field rather than duplicating it and dropping it, but since that's not possible (or documented!), my code is almost exactly the same as yours, above.  I added a lookup table, but mostly I've just commented every other line.

                                                                   

                                                                  SET HidePrefix = '%';

                                                                   

                                                                  Orders:

                                                                  FIRST 100000 LOAD * FROM "Raw Orders.qvd" (qvd);


                                                                  Customers:

                                                                  JOIN FIRST 100000 LOAD * FROM "Raw Customers.qvd" (qvd);

                                                                   

                                                                  DatePrefixes:

                                                                  LOAD * INLINE

                                                                  [

                                                                  %KeyFieldName, %Prefix

                                                                  %CancellationDateKey, Cancellation

                                                                  %ConversionDateKey, Conversion

                                                                  %ExpirationDateKey, Expiration

                                                                  %OrderDateKey, Order

                                                                  %DatePaidKey, Paid

                                                                  %HistoryDateKey, History

                                                                  %SetUpDateKey, Set up

                                                                  %ShipDateKey, Shipped

                                                                  ];

                                                                  FOR _tableNum = 1 TO NoOfTables()  // For each table in our file...

                                                                      LET _tableName = TableName(_tableNum - 1);

                                                                     

                                                                      FOR _fieldNum = 1 TO NoOfFields('$(_tableName)')  // For each field in this table...

                                                                          LET _dateFieldName = FieldName(_fieldNum, '$(_tableName)');

                                                                         

                                                                          IF WildMatch('$(_dateFieldName)', '%*Date*Key') THEN  // This field indicates a date key; import the Dates table, with a prefix to maintain uniqueness

                                                                              // The name of the master table's key field determines the human-readable prefix

                                                                              LET _prefix = Lookup('%Prefix', '%KeyFieldName', _dateFieldName, 'DatePrefixes');

                                                                             

                                                                              // Load the Dates file, with the key aliased to match the field in the master table to which we're going to join

                                                                              LET _dateTableName = _prefix & ' date';

                                                                              [$(_dateTableName)]:

                                                                              LOAD *, "%DateKey" AS "$(_dateFieldName)" FROM "Raw Dates.qvd" (QVD);

                                                                             

                                                                              // Discard the old key field; note that we can't simply rename it, we have to duplicate it and then drop the old field, because QlikView does not permit renaming keys

                                                                              DROP FIELD "%DateKey";

                                                                             

                                                                              // Rename every field (except the key) in the newly-loaded dates table

                                                                              FOR _dateFieldNum = 1 TO NoOfFields('$(_dateTableName)')

                                                                                  LET _oldDateFieldName = FieldName(_dateFieldNum, '$(_dateTableName)');

                                                                                  IF _oldDateFieldName <> _dateFieldName THEN

                                                                                      LET _newDateFieldName = _prefix & ' ' & _oldDateFieldName;

                                                                                      RENAME FIELD "$(_oldDateFieldName)" TO "$(_newDateFieldName)";

                                                                                  ENDIF  // If this is not the key

                                                                              NEXT  // Next date table field

                                                                          ENDIF  // If the field name indicates a date key

                                                                      NEXT  // Next field in the master table

                                                                  NEXT  // Next master table

                                                                   

                                                                  I wouldn't call it tortured, but yeah now I see why you had what seemed like an odd construct in your original script.  Despite this painful detour, your code got me exactly the solution I wanted, so thank you.

                                                              • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                                                                John Witherspoon

                                                                I'm unclear if we're in agreement or not on the bloat.  While there are advantages to just putting all the date fields in the main fact table QVD, I agree that adding 100 MB or 1 GB to our main fact table was bloat.  That was in the first paragraph.

                                                                 

                                                                What I meant by user application bloat (second paragraph) was that if we loaded ALL calendar fields for ALL dates in a user application, this was likely to be a lot more fields than the user application needed.  User applications would ideally only load fields they need.  So whether we use a calendar script, or LOAD * from OrderDateCalendar.qvd (QVD), we'd be causing user application bloat.  But also as I said, "It probably isn't that big a deal in practice".  As long as they're on separate tables, it won't take much space.  It's really only a problem if they're then joined with the main fact table.

                                                                 

                                                                Date are already stored as integers, as the number of days since December 30, 1899.  And as long as you have separate calendar tables, they won't take up much space.  This is also what you'd do if load times are the top concern.  In that case, you wouldn't put all the date fields in the fact table.  You'd either have one calendar QVD and rename as you read in, or multiple calendar QVDs.  You would not join these to the fact table, but just let the association by field name take care of it.

                                                                 

                                                                So now I'm thinking your best answer is to have one calendar QVD, nail down the script code, and use it as an include to automatically generate calendars for every date field in the user application.  You get more fields in user applications than you want, but they're on small tables, so it won't affect overall size or performance very much.  You get to keep your fact tables as small as possible.  Performance, file size, and RAM used should all be pretty good.  You maintain your calendar fields in one place.  You maintain your calendar script in one place.

                                                                 

                                                                I need to look up how to include script, though.  I've never done it.  Looks like it would be like this?

                                                                 

                                                                $(Include=GenerateCalendars.qvs)

                                                                 

                                                                I need to fiddle with this a little, because this is starting to sound like something I should consider doing myself.

                                                                 

                                                                Wait... we may be reinventing the wheel, or at least simultaneously inventing the wheel.  I bet there's a calendar in Rob Wunderlich's QlikView Components open source project (now in beta).  I'm off to have a look...

                                                                 

                                                                http://code.google.com/p/qlikview-components/

                                                                  • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                                                                    John Witherspoon

                                                                    John Witherspoon wrote:

                                                                     

                                                                    Wait... we may be reinventing the wheel, or at least simultaneously inventing the wheel.  I bet there's a calendar in Rob Wunderlich's QlikView Components open source project (now in beta).  I'm off to have a look...

                                                                     

                                                                    http://code.google.com/p/qlikview-components/

                                                                     

                                                                    OK, Rob's QlikView Components project does indeed include calendar script.  You call a script function with a min date, max date, table name, and field prefix.  You can establish the min and max date automatically from your data using another call.  So I think it looks like this:

                                                                     

                                                                    CALL Qvc.GetFieldValues('vDate','Order Date');
                                                                    CALL Qvc.Calendar(vDate.Min,vDate.Max,'Order Date Calendar','Order');

                                                                     

                                                                    Then repeat for each date field.  And I suspect we could wrap it in our loop that detects date fields in our model if desired.

                                                                     

                                                                    What seems to be a significant limitation is that it is autogenerating the calendar in the included script, and of course this included script is part of the open source project.  It's just a script file, so you can override it to put in all your own field names and definitions, but I think you'd have to store that off somewhere and copy it back in every time you updated the project, while making sure you weren't breaking anything.

                                                                     

                                                                    I definitely need to look into it more carefully.

                                                                    • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                                                                      Jonathan Shaltz

                                                                      Agreed on all that re: bloat.

                                                                       

                                                                      Interesting code from Wunderlich.  Too bad I didn't find it in my Googling.  I've had a hard time finding answers to QlikView questions via plain ol' web search, maybe I'm just not using the right key words.

                                                                       

                                                                       

                                                                      Your syntax is correct for the include command.  It gave me some trouble until I discovered that, unlike the SET and LET commands, and unlike what the help file shows, ($Include) will break if you put a space before the equals sign.  I suspect it's a conspiracy to make me lose the rest of my hair.  But it works, so I'm happy!  Now I'll go do something fun, like wrestle a rabid crocodile in a phone booth.

                                                                        • Best Practices Re: a Calendar Table and Multiple Date Fields
                                                                          John-Paul Della Putta

                                                                          Am I missing something or do we need to do this simply because QV isn't very friendly with the way it handles dates?

                                                                           

                                                                          All this for only a drill up drill down?

                                                                          I mean the effort required to create a separate calendar for each date field if you use Rob's script or John's isn't too much, but essentially all you are creating is a series of calendars which allow you to drill up and down date ranges.

                                                                           

                                                                          Should be easier

                                                                          If you look at the way Tableau deals with dates, it seems simpler. What happens is that Tableau sees a date field and automatically makes it a drill up and down field. So you drop in a date, say Order Date and then you automatically get Order Day / Week / Month / Qtr / Year. Now if you have different Fiscal years, this might need some tweeking.

                                                                           

                                                                          Each Calendar could be different

                                                                          I would agree that creating a new calendar for each field may be useful. In the example of Ordered / Shipped / Invoiced, it might be the case that different periods are used for these business processes. This could easily be the case in a large multi-national environment where invoice date conforms to a Fiscal period at head office in the USA and the order date falls into a different period, so you may have the date fields linking to a totally separate calendar table.

                                                                           

                                                                          A final question - do you stick with Set Analysis or Mix it Up?

                                                                          Let's say I created a document which tracks Orders / Shipped / Invoiced. That lends itself nicely to a central concatenated FACT table as the columns for each process are similar. In this case, we can also use a central DateKey which would correspond to the transaction date for each process.

                                                                          In this case, you can easily create a few listboxes linking to the central calendar for Year/Month/Day. When a user clicks on a day, you will get all the Orders, Shipments and Invoices for that day. Remember, we did a concatenation and set the DateKey to be the transaction date for each transaction type.

                                                                           

                                                                          However, back to John's comment in a previous part of the thread:

                                                                           

                                                                          • Do your users want to be able to select December 1, 2011, and see all orders placed OR shipped on that date?  Then create a single generic calendar linked by date type.
                                                                          • Do your users want to be able to see orders that were both placed on November 15, 2011 and then shipped on November 20, 2011?  Then create separate calendars.

                                                                           

                                                                          The user may be wanting to see all orders for one day , then the corresponding shipments and invoices. Set analysis will work best for that.

                                                                           

                                                                          Personally, I am leaning towards using set analysis everywhere, but it does make things more complex mainly, I think for the reason that you have to anticipate what the user is going to do much more.

                                                                            • Best Practices Re: a Calendar Table and Multiple Date Fields
                                                                              John Witherspoon

                                                                              QlikView handles dates quite poorly in my opinion.  I suspect that QlikTech made the conscious decision that a date field was going to just be a simple field like any other, that they would never create any fields or data or associations for you just because something was a date or for any other reason.  I respect that position, but strongly disagree with it.  Dates and times MEAN something.  I don't know exactly how I'd like to see them work, but certainly they could work better than they do.

                                                                               

                                                                              Set analysis is an overused tool in my opinion.  It's a great tool, but it's not the best solution to all problems.  Data model changes are often a better solution.

                                                              • Best Practices Re: a Calendar Table and Multiple Date Fields

                                                                what if the users want to see both shipped and placed in same chart.  I.e.The axis of the chart is the date.  and you want a bar to show Qty Ordered and Qtry Shipped per date.  How will this work if your shipped date and ordered date fields are two different named fields?  What we are doing now is creating a concatenated table with a common date "Report Date" and bring in a row for each Shipped and Ordered and using the script to assign date value:  Order Date as Report Date for order load and then Concatenate the shippe load with Shipped Date as Report Date.  We would either add a field to each load with Type = Order, Type = Shipped  or add a metric column to store a count for Qty Shipped, Qty Ordered.  Is this the only way?

                                                  • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                                                    Fabien Chung

                                                    hI,

                                                     

                                                    First of all, englsih is not my mother language so i apologize in advance for mistakes.

                                                     

                                                    I'm really interrested in your conversation because i face the same problem with calendar. Should i use one master calendar ? Should i build different calendar, one for each fact table ?

                                                     

                                                    In my first application we had registration calendar, first order calendar, sales calendar, AsOfCalendar (to calculate for each date  the week, the month before, the year same date before (18/07/2012 >> 18/07/2011), the same day las year (18/07/2012 >> 19/07/2011) -365 days). I had also to manage calendar year and fiscal year.

                                                     

                                                    For my second application, i have procurement calendar, order calendar, and so on.

                                                     

                                                    So as you can you can we choose multiple calendar. But how do manage all these calendars in your application ? Because with the associative model, if we pick a date in a calendar that impact all tle model.

                                                     

                                                    A lot of usesr forgot to undo selection and complained of missing data (first order sales calendar was selected and when they wen into the sales tab they had only first order sales) So i used trigger to clear calendar and copy the context (firstordersalescalendar.year = 2012 is cleared and sales.year = 2012 is selected and same for quarter, month, week, day ..).

                                                     

                                                    But for me it's not a good solution because it's hard to maintain (sometimes i had to put triggers on button, sometimes on a selected field and so on) .

                                                     

                                                    what do you think about that ? what is the best solution for you ?

                                                     

                                                    Regards,

                                                      • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                                                        Jonathan Shaltz

                                                        It sounds like your calendar solution is fine, the real problem is users forgetting about the selections they already have.  To some extent, this is just something they'll have to get used to in QlikView.  You can help them by making their selections prominent; for example, put the date pickers next to each other, so it's obvious when more than one has a selection.  Also, put a Current Selections box on the report, and put it somewhere noticeable.  Show them the Back and Clear buttons.

                                                        • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                                                          John Witherspoon

                                                          I guess you have registration data on one tab, order data on a second tab, and sales data on a third.  You have three separate calendars and use triggers to copy the calendar selections from one calendar to the next as you switch tabs.  That works, but I understand why you might not like it.

                                                           

                                                          I think you should try a master calendar.  With the master calendar, you wouldn't have to change selections when you change tabs.  It would be simpler.  It might not work like you want, but it is worth a try.

                                                           

                                                          Jonathan's suggestion sounds good too.  You could put every calendar on every tab.  That should make it clear to the users what they need to do.  It can just take a lot of space.

                                                           

                                                          The best solution depends on the application.  I've used master calendars.  I've used separate calendars.  I've used separate calendars with triggers that copy selections from one calendar to another.

                                                            • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                                                              Fabien Chung

                                                              Thank you for your quick and helpful answer. I wiil try to enhance the calendar selection, but i don't know if i show every calendar on every tab is a good solution, risk of overload. I should try

                                                               

                                                              But how does a master calendar work ?  How i understand it, it's a detached calendar, but how do you link it to the other tables ? triggers ?

                                                                • Re: Best Practices Re: a Calendar Table and Multiple Date Fields
                                                                  John Witherspoon

                                                                  Please see the "testMultipleDatesMultipleTablesOneCalendar3.qvw" example earlier in the thread.  It isn't detached, though that CAN cause loops in your data structure, so isn't always a good solution.

                                                                   

                                                                  A detached calendar, or "date island" as we often say around here, is another possibility.  It's one I very rarely use due to performance issues and expression complexity, but it's doable.  So say you have RegistrationDate, OrderDate and SaleDate.  Now you make a new field, Date, and that's the field you let them select (along with Week, Month, Year, whatever you need).

                                                                   

                                                                  Then in all of your charts, link up this island date to the dates you care about with an if() statement, such as sum(if(SaleDate=Date,SaleAmount)).  (Edit: To clarify, as Anosh Nathaniel says in the next post, the if() and associated performance penalty is only necessary on charts with the Island Date as a dimension.  Other charts can use set analysis, as then all you're trying to match is the selections, not the dimensions.)

                                                                   

                                                                  Actually, see the attached example.  I expanded the earlier example to include an island calendar for comparison.