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: 
Dan_54S
Contributor II
Contributor II

Issues with linking dates from separate tables

Hi, I am trying to measure Orders per Product View. I want to take the total number of times all products in a store were ordered in a given time period, and divide by the number of times all products were viewed in that same time period. In order to create visualizations over time and filter by dates, I am trying to create a single date field for this.

Currently I have table "productviews_by_date":

                                  product id

                                  view_date

                                  viewcount

and an orders table entitled "orders" that includes:

                                  order id

                                  product id

                                  order date
These both link to "Product", which includes:

                                  product id

data modeldata model

 

I followed the instructions in Canonical Date to create master calendars for "productviews_by_date" and "orders", and then created a DateBridge table from "product" using an applymap to "productviews_by_date" and "orders". Finally, I created a Canonical Calendar from the DateBridge table. 

I'm getting weird date results from the Canonical Calendar, such as varying date formats, dates with no orders or views, and only some of the orders for each day when filtering. Is the issue here maybe that the DateBridge went through the product table based on product id, which isn't tied to specific dates, and is two tables removed from the orders table? If so, what might be the solution? All of the load scripts were copied directly from Canonical Date.

Labels (3)
2 Replies
Lisa_P
Employee
Employee

Check your date formats of view_date and order date. They should be the same format.
Dan_54S
Contributor II
Contributor II
Author

I checked the date formats, and that doesn't seem to be the reason for missing data. 

My DateBridge table is based on my products table, and has three fields: product id, canonical date, and date type (order or impression). Looking at the data, the table is showing for each product id and date type just one date (apparently the first relevant date), rather than all dates that product was viewed or ordered.

DateBridgeDateBridge

 

How can I show for each product id every date the product was viewed or ordered? Can this be fixed in the load script using some sort of grouping or concatenation?