Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
ferha_jafri
Creator III
Creator III

I am not getting the result as it should be after creating a master calendar

Hello everyone,

I need help with regards to data modelling , in my case i have 13 tables out of which 5 are transaction tables in these 2 tables dont have relevant dates as the data feed there on the basis of month and year and then we have 3 tables in which we have date my issue is when i am creating a master calendar by concatenate these dates they not mapping correctly with all the tables as for some userid if the date in one table is 1/1/2013 for the same user there is some other date as 2/3/2009 due to which the data is not coming correctly i want to know how should i perform a data modelling in this case so that the master calendar synchronize with all the tables.It is urgent,please reply if someone knows the answer.

Thanks in advance

Ferha Jafri

6 Replies
swuehl
MVP
MVP

You might want to use several master calender for your distinct semantic dates.

Tutorial - Using Common Date Dimensions and Shared Calendars

cn_sa_dev
Partner
Partner

Hi,

Sounds like you have 2 issues - one is some tables have YYYYMM format and others have YYYYMMDD format.  The other issue is different date types eg YYYYMMDD, DDMMYYYY, etc.  Is that correct?

For the first issue you'll need two different keys that connect the transactional data tables to your master calendar, one at YYYYMM level and one at YYYYMMDD level.  This way selecting on item at YYYYMM level, lets say 201310 returns YYYYMM01, YYYYMM02, YYYYMM03...YYYYMM30 or 20131001, 20131002, 20131003...20131030.

So your master calendar will look like this:

%YM_Key%YMD_KeyDate
201310201310012013-10-01
201310201310022013-10-02
201310201310032013-10-03
..
201310201310302013-10-30

For your second issue you need to convert all the dates to the same format as per example below.

Different formatsStandardised formatCalculation
01-10-20132013-10-01=makedate(right(column1,4),mid(column1,3,2),left(column1,2))
2013-10-012013-10-01Correct format

You can also convert all your dates to the number format (2013-10-01 = 41548) by using the floor function.

Hope that answers your question?

Chris

ferha_jafri
Creator III
Creator III
Author

Hello ,

Yeh you are correct but what i did i convert all the dates to num as all the dates have different formats ,now my issue is when i create a master calendar dates in the tables cannot connect to the master calendar as there is one table suppose event in which we have activity dates now if i take one example as corresponding to user id 1010 event date is 1/1/2013 and in master calendar we have date 3/5/2009 so the mapping is not coming correctly.Any idea what should i do in this case.

Thanks in advance

Ferha Jafri

Not applicable

If you are sure it's not to do with incorrectly fomatted dates, as chris points out above, you might need to look in to how the tables are relating in the data model. 

cn_sa_dev
Partner
Partner

Hi,

If the dates mean different things (for example transaction date VS posting date) it would be better to have two or more "master" calendars for each functional area (one for transaction dates and one for posting dates).  First you need to sanitize the data as discussed and then you can model it as per Swuehl's link above - one master calendar for each area where there are multiple dates for a userid as per your example.

Chris

cn_sa_dev
Partner
Partner

Hi Ferha,

Did this answer your question?