Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

qlik_hot
New Contributor

Multiple facts different dates but need to select data

Hi There,

hopefully someone can help with this extremely complex question (it may well not be able to be answered).

i currently have 2 fact tables which both contain different date fields however there are some keys that are common in both fact tables (i will try to draw something to explain this) however not all key fields will be in both fact tables.

what i want to do is in theory combine the 2 fact tables and if i select a date from fact 1 i want to keep the association to fact 2.

example facts.PNG

 

fOr example

i select '01/01/2019' from date field 1 i want to show all corresponding values in fact 1 & fact 2 that link based on the key1 field. so in the example shown i select key1='ABC123' and date1='01/01/2019' which sums the value £100 from field 1, but i also want to show the sum of the date1 = '01/02/2019&01/03/2019' and date3='01/07/2019' because these have the same key1 but a different date. so there is still assocation between the key1 field but different by date. so the sum of field 1 = £300 and sum of field 2 = £100 even though i have selected 01/01/2019

example facts2.PNG

 

as i say i dont know if this is possible or if i need to go down the link table route but based on many dimesion table and 2 fact tables and many calendars this seems like the data model could become very slow with c200million rows in there. i am just looking to keep the association even though im selecting a date

 

any help would be great

Labels (3)
3 Replies
Partner
Partner

Re: Multiple facts different dates but need to select data

Hi,

Do you mean something like this?

MultiFacts.PNG

 

 

Thanks and regards,

Arthur Fong

Partner
Partner

Re: Multiple facts different dates but need to select data

Qvw is attached as reference.

Thanks.

qlik_hot
New Contributor

Re: Multiple facts different dates but need to select data

yes kind of, thats great in a chart/table, but im hoping more to keep the association across the whole app, so KPIs also saying £300 and £100 not just in the chart.

i am thinking there isnt anything easy and will require a link table, but the model will be huge and lot going through the link table. plus there are multiple calendars coming off each fact.