Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
m_perreault
Creator III
Creator III

Data Modeling Question - Mixed Granularity

I have two tables of mixed Granularity and mixed date range and am struggling to figure out the best way to model them.

Table 1 is a Transactions table.  This Table has a Transaction Date which can be any date and has data back to 2010.  See below for all Transactions by Owner = A ID = 1A.  Note there are Months When there are no Transactions

OwnerIDTransactionDate
A1A1/1/2010
A1A3/2/2011
A1A6/16/2012
A1A7/20/2013
A1A4/29/2014
A1A10/30/2015
A1A12/4/2016
A1A5/5/2017
A1A6/6/2018

 

My second tables is a "Positions Table".  The Positions table has a Month End Date and goes back to 2015 Below is an example of Positions for Owner = A and ID = 1A

 

OwnerIDMonthEndDateValue
A1A1/1/20131
A1A2/1/20132
A1A3/1/20131
A1A4/1/20133
A1A5/1/20134
A1A6/1/20135
A1A7/1/20136

 

I want to Transactions by Owner & ID & MonthEnd(TransactionDate) to its Owner & ID & MonthEndDate in the Positions table.  I am having issues because of the mixed Date Ranges between the tables.  

If I select an ID from the Positions table I only get its Transactions going back to 2013. 

 

I thought about creating a Concatenated Fact table but I then ran into the issue of if I select a TransactionDate, I wont see the value from Positions for the Month End of that Transaction.

 

Any suggestions on how best model this would be much appreciated!!!

 

Thanks,

Mark

Labels (1)
0 Replies