topic Re: Need Help with Data Model/Dates Issue in QlikView

Need Help with Data Model/Dates Issue

ramyasaiqv — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Need Help with Data Model/Dates Issue

marcus_sommer — Mon, 20 Feb 2017 20:40:21 GMT

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

Re: Need Help with Data Model/Dates Issue

ramyasaiqv — Tue, 21 Feb 2017 01:22:17 GMT

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.

Re: Need Help with Data Model/Dates Issue

marcus_sommer — Tue, 21 Feb 2017 06:41:54 GMT

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

- Marcus

Re: Need Help with Data Model/Dates Issue

Tue, 21 Feb 2017 08:10:26 GMT

Could you please share the application ..if possible

Re: Need Help with Data Model/Dates Issue

ramyasaiqv — Wed, 22 Feb 2017 03:10:09 GMT

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

Re: Need Help with Data Model/Dates Issue

ramyasaiqv — Wed, 22 Feb 2017 03:12:39 GMT

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,

Re: Need Help with Data Model/Dates Issue

marcus_sommer — Wed, 22 Feb 2017 08:14:02 GMT

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

Re: Need Help with Data Model/Dates Issue

ramyasaiqv — Wed, 22 Feb 2017 13:23:28 GMT

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,

Re: Need Help with Data Model/Dates Issue

marcus_sommer — Wed, 22 Feb 2017 15:04:16 GMT

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

Re: Need Help with Data Model/Dates Issue

ramyasaiqv — Thu, 23 Feb 2017 04:08:46 GMT

Marcus,

Thank you for your help, i'm following the similar approach but still it's not working. My lead will be back from vacation tomorrow will take my lead's help to figure it out. I really appreciate your help. Thank you,