topic Re: Creating Proper Data Model in QlikView

Creating Proper Data Model

Maverick999 — Sat, 16 Nov 2024 02:44:23 GMT

Hello,

I would like to know the right way to work with this data. I loaded 4 different tables, which all have a client ID field in common. What is the best data model to aim for and what types of data transformation should I do to achieve it?

Also, I am familiar with the concept of Master Calendar but how can I know if I should create a master calendar PER each date field ?

Thanks a lot.

Re: Creating Proper Data Model

dplr-rn — Wed, 14 Aug 2019 16:40:57 GMT

Ideally Always aim for a star schema as much as possible.

regarding the date field creating master calendar for each one depends on your requirement. but if you need to use canonical date concept along with it.

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

Re: Creating Proper Data Model

Maverick999 — Wed, 14 Aug 2019 18:41:04 GMT

Let me update my post:
I have tried to create a star schema by concatenating by adding the « ID » keys of the different tables
I get the below schema. I also added a Master Calendar based on the date client created.

Is this schema correct?
Nevertheless, what I am trying to achieve is create 2 graphs:
- One where I would have The amount of unique clients who had spent between 1-100€, 100-300€ and +300€. For that I created a new dimension using if function in the table Order and I planned to use this dimension
- One where I can identify the clients that made an order less than 4 days after receiving a specific canal (email or sms)

Re: Creating Proper Data Model

Arthur_Fong — Thu, 15 Aug 2019 06:19:37 GMT

Hi Maverick,

After going through your second model, it seems that the second model you shown are not the same in terms of table columns and keys if compared to the first model.

Please provide your qvw so that we can go through the data model for a better solution.

Thanks and regards,

Arthur Fong

Re: Creating Proper Data Model

Maverick999 — Thu, 15 Aug 2019 17:09:54 GMT

Hello JK,

Please find attached the qvw and the data.

I put it back as the initial state, I would like to know what data modelling to do from this kind of data. Four tables linked by a key.

Thanks in advance

Re: Creating Proper Data Model

Arthur_Fong — Fri, 16 Aug 2019 00:25:31 GMT

Hi Maverick,

Attached is the qvw with my point of view for scripting this data model.

Your approach of creating a master calendar is good. However, you have to create linkage from the master calendar date towards all other table date.

I did this by creating a link table to link all 3 facts using client_id and date as key, as these are the common fields that can be found in the 3 fact tables.

Giving the 3 dates a same name as the MasterCalendar's date (DateCal) will associate the link table with the MasterCalendar now.

With this, you may create selection panels for (Year,Month,Date) and the dates for the other 3 fact tables will also be reflected as well.

Hope this helps in your development.

Thanks and regards,

Arthur Fong

Re: Creating Proper Data Model

Maverick999 — Fri, 16 Aug 2019 15:42:27 GMT

Hello Arthur, thanks a lot for explaining me your approach and modifying the data model.

It looks like it makes great sense, however when I am trying to create a simple graph to show how many clients there were each year. I use in the expression: = count( DISTINCT id_client) and as a Dimension "Year".

But in the graph, the Year only starts at 2017 when I see in my data there there already clients (id_client) back in 2015 (client tab in excel).

So I am thinking I might be missing something related to the MasterCalendar isn't it?

Re: Creating Proper Data Model

Arthur_Fong — Mon, 19 Aug 2019 00:52:50 GMT

Hi Maverick,

Sorry that I didn't notice that your client table date is a timestamp based date.

So the formatting didn't match with the one in Link table, which causes all calendar fields that has association with client table not able to be linked.

Refer updated qvw with the updated script.

Thanks and regards,

Arthur Fong

Re: Creating Proper Data Model

Maverick999 — Mon, 19 Aug 2019 19:02:20 GMT

Thanks a ton Arthur!

I am struggling on how to use it properly since I am bit confused about which ID to use (KeyLink, Client_ID etc.).

For example, if I want to make a graph to show which are the campaigns that led to the greatest number of clients making an order less than 4 days after being exposed to the campaign.

The date when a client has been exposed to a campaign being the column "date" in CRM table and the date when a client made an order being the column "date_cmd" in the "achat" table (in the excel).

Thanks a lot for your help in advance.

Re: Creating Proper Data Model

Arthur_Fong — Mon, 19 Aug 2019 23:57:06 GMT

Glad that it helps.

Do mark the solution provided as correct for others reference.

Thanks and regards,

Arthur Fong

Re: Creating Proper Data Model

Maverick999 — Tue, 20 Aug 2019 05:43:20 GMT

Hello,

To confirm this data model is correct, I need to understand how to use it then.

I am not sure how to use it properly since I am bit confused about which ID to use (KeyLink, Client_ID etc.) depending on the requirement.

For example, if I want to make a graph to show which are the campaigns that led to the greatest number of clients making an order less than 4 days after being exposed to the campaign.

The date when a client has been exposed to a campaign being the column "date" in CRM table and the date when a client made an order being the column "date_cmd" in the "achat" table (in the excel).

Thanks a lot for your help in advance.

Re: Creating Proper Data Model

Arthur_Fong — Wed, 21 Aug 2019 06:31:57 GMT

There you go:

Do take a look on how the logic is defined in the script.

Once the flag is created, you can sum it up and get the counts of successful campaigns.

This screenshot is took at the last tab called "Sheet".

Thanks and regards,

Arthur Fong

Re: Creating Proper Data Model

Maverick999 — Wed, 21 Aug 2019 10:15:51 GMT

Thanks a lot Arthur! It works greatly, I marked it as solution.

I am not sure of the necessity of KeyLinkCLIENT, KeyLinkOrder and KeyLinkWEB but I will investigate deeper the script.

I would have much more questions regarding the proper data model to aim (like for example I thought we should always aim for a star schema model but here it does not seem the case), but I should save these for another post probably.

Thanks again, have a great day.