topic Where to link budget numbers? Help with concatenation in QlikView

Where to link budget numbers? Help with concatenation

andersonc — Wed, 20 Dec 2017 14:45:04 GMT

Hi, I'm having an issue with bringing in budget and KPI goals into my data model. I've read Henric's article on generic keys and I'm not sure if that would be a possible solution here.

My Fact table (FTable) is linked to the Site table by Site_Key. The site table contains sales rep and manager information. My Fact table has sales transaction details by day.

I'm trying to incorporate KPI goals. The KPIs are manager based and I can work with them by day/week/month so granularity isn't really a concern here. First I tried concatenating to the Site table, but then I couldn't link to my calendar. When I tried concatenating the goals to the Fact table, I couldn't link to any manager. I have multiple calendars since each sales transaction has an order date and an effective date.

Sorry I am new at this and struggling to come up with a solution. Thank you kindly.

Below is a picture of my data model.

Re: Where to link budget numbers? Help with concatenation

Anil_Babu_Samineni — Thu, 21 Dec 2017 01:05:12 GMT

Could be the KPI's are not having any primary key to fact and/or Manager for Rep table?

Re: Where to link budget numbers? Help with concatenation

marcus_sommer — Thu, 21 Dec 2017 06:42:18 GMT

I think your approach of concatenating the budgets to the sales should work whereby you will need to prepare/enrich the data before so that they also contain the proper keys to your associated tables.

- Marcus

Re: Where to link budget numbers? Help with concatenation

andersonc — Thu, 21 Dec 2017 14:47:07 GMT

Thank you Anil. That is correct, the two main tables are linked by Site Key and the KPIs have no Site Keys associated with them.

Re: Where to link budget numbers? Help with concatenation

andersonc — Thu, 21 Dec 2017 14:50:13 GMT

Thank you Marcus. I am afraid of getting synthetic keys because I feel I'd need to link my KPI data to both tables. The only field that currently links them is Site Key and the KPI data has no Site Key as it is manager based. Maybe I should create a dummy Site Key?

Re: Where to link budget numbers? Help with concatenation

marcus_sommer — Thu, 21 Dec 2017 15:13:23 GMT

Yes, you will need to create the proper keys and maybe also some kind of distribution of the data to the right granularity.

- Marcus

Re: Where to link budget numbers? Help with concatenation

Anil_Babu_Samineni — Thu, 21 Dec 2017 15:21:46 GMT

You can make one dummy field using ApplyMap() to link from that table