Right way to create a table without dimension

[#1]

Hi, I want to create a table without dimension.

Here is the problem, I have 3 tables, but because of the table dimension in qlik, I can't join them into 1 table.

1st table dimension: month that users are created

2nd table dimension: month that users drop out

3rd table dimension: month that users signed up for program

The reason I like to join them together because there I want to create secondary column (estimated active users & churn rate) using these primary columns

So as you can see the problems

1. 3 tables with 3 different dimension in qlik

2. Not all 3 tables have value for every month, thus making the secondary column hard to compute

3. Not sure how to implement the computation

I have attach the sample in excel sheet. I would really appreciate any help, thanks.

[sunny_talwar]

And why are you not joining them on the MonthYear dimension? Is there a reason?

[#3]

Hi, the MonthYear dimension is not the same.

Table 1 MonthYear is based on profile created date.

Table 2 MonthYear is based on last read date.

Table 3 MonthYear is based on sign up date.

So they look like they are same MonthYear, but they are not. If I add table 2 or 3 to table 1, the values will be wrong because the MonthYear is different.

[Oleg_Troyansky]

Hi Shawn,

ideally, I'd recommend to use data modeling techniques and combine the three dates that you listed, in a single Date field that could be linked to a single MonthYear field in your calendar.

However, if you really don't want to combine them and you simply want to create a detached table with all the summaries, you can use either joining or mapping to populate the table:

1. Create a list of all MonthYear valued in your time interval.

2. Either JOIN the three tables to the list of distinct MonthYears, or use MAPPING tables and Applymap to populate the corresponding values.

Keep in mind that the resulting table will be detached from the rest of your dataset - simply because you can't link a single MonthYear to three different fields at the same time.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView an...

[#5]

Hi!

I would love to create a single Date field that could be linked to a single MonthYear field in my calendar. Currently I do not have a calendar. Based on the online resources, the calendar will be created based on a certain column. But because my Mysql database is a many to many database, there is no such column where all the dates can be found. Instead I have many tables, with many different column containing dates with different meaning. Is there a way to create a calendar without basing it on any column? For example, creating a calendar from 01/01/2015 to today?

[Oleg_Troyansky]

Hi Shawn,

yes, you can create a detached calendar, so called "Date Island", however I typically don't recommend that solution for its poor performance. It can only work on very small datasets.

Your better option is to learn how to use QlikView data modelling techniques to transform your "may to many database" into an analytical data model.

cheers,

Oleg Troyansky

[#7]

Thank you!