Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ramyasaiqv
Creator II
Creator II

Need Help with Data Model/Dates Issue

Hi All,

I’m trying to fix an issue with dates and tried as much as I could but still not able to fix the issue. So I’m posting it here hoping someone will help me. As this is a data model issue it might take some time for you guys to help me with the issue so please help me in identifying the issue.

Basically, I’m trying to join a budget data table with another major “sales” fact table as I need to create a report with combination of fields from these 2 tables. As these 2 tables are not at the same granularity and neither of the tables have the date field that I need. I used 2 other calendar_dim and fiscal_dimension tables from same schema, joined these 2 tables got all the date related fields. Finally joined this date (Fiscal)table to main fact “sales” using the calendar_date_id.

Now the issue is when I create the report the data is not associated with the right time dimensions

Instead of 2016 Fiscal (2015 half and 2016 half calendar year) year, data is associated to 2014 Q3 which is not correct. So please can someone point me out what’s wrong with my code? Thank you,

I have attached the sample qvw and also the excel files with data.

Thank you,

Ramya

10 Replies
marcus_sommer

I use in a similar case a different approach and just concatenate sales- and budget-tables whereby I create per makedate(Year, Month, 1) a date which resolved the different granularity on the date-level. This method is quite easy and performed fast within the script and the UI.

- Marcus

ramyasaiqv
Creator II
Creator II
Author

Hi Marcus,

Thank you i will try that but actually i need to use both Fiscal and Calendar Year/Date/Month on the app UI over multiple tabs so i'm not sure if that works for all.

marcus_sommer

Connecting several calendars won't be different if you merged your tables per join or per concatenate.

- Marcus

Not applicable

Could you please share the application ..if possible

ramyasaiqv
Creator II
Creator II
Author

Hi Marcus,

Thank you, yes that's why concatenated the tables and used applymap to bring in the date_id to the budget table but it's not working the way it was suppose to work. if you get a chance please can you have a look at the attached qvw. Thank you,

Ramya

ramyasaiqv
Creator II
Creator II
Author

Hi Manoj,

Thank you for the response. I have attached the qvw (Dates_issue.qvw) and also the excels (data). please let me know if you still cannot see the attachment. Thank you,

marcus_sommer

You need to create a date within the budget-load with something like: makedate(Year, Month, 1) and this date-field get the same name like this from the sales-load and mustn't be mapped to the calendars else there should be a normal table-association work.

- Marcus

ramyasaiqv
Creator II
Creator II
Author

Marcus,

Thank you again for the response.

Here is what i'm trying to do please let me know if that makes sense

I'm trying to join /concatenate 'sales' and 'budget' table, both tables have time dimensions(year and month) but Sales table has both Fiscal and calendar year and  month and Budget table has only calendar year and month and none of these tables have date (day ). I'm trying to build a kind of master calendar table using 2 other calendar tables('calendar' table and 'fiscal calendar' table). These 2 calendar tables have all the time dimensions including 'Date'. So i joined the 2 calendar tables named it as "Fiscal". Then joined this 'Fiscal' table to 'Sales' using a key field 'calendar_id.'  As budget table doesn't have calendar_id, used applymap to bring in calendar_id to budget table. does that makes sense? Really appreciate your help. Thank you,

marcus_sommer

I think your tables respectively load-structure should look like this (here in a simplified manner):

Facts:

load ID1, ID2, Date, Sales from Sales;

     concatenate

load ID1, ID3, Date, Budget from Budget;

MasterCalendar:

load * from MasterCalendar;

FiscalCalendar:

load * from FiscalCalendar

whereby the association between the fact- and the dimension-tables is Date. If there is no date-field within Sales and/or Budget you need to create them from the year- and month-fields.

- Marcus