topic Re: Re: Always show objective in QlikView

Always show objective

fkeuroglian — Wed, 12 Feb 2014 23:58:25 GMT

Hi, people

I have a problem and i would like to know how recommend you to solve this :

I have a fact table(sales table) that have all the facturation, this fact tables have some key , like Canal, zone and others , now i have a new requirement and i have to add Objective to this Canal and zone.

I do it, but my problem is that i can see the future objetive, if i have no facturation ... for example , i cant see the objective of march or april 2014 because in my fact table there is no facturation about these months.

i create a table that have a relation with the fact table with a composed key.

what change in my model do you recommend and if you have an example would be better!

sorry for my english, hope you can understand me!

thank you a lot

Fernando

Re: Always show objective

Thu, 13 Feb 2014 00:39:13 GMT

As per your statements the Objective data must have the date field and you fact table must have the date field.

Create the link table that connects you fact table and future Objectives and Calender.

TempLinkTable:

LOAD DISTINCT Canal, Zone , Date Resident FactTable;

Concatenate (TempLinkTable)

LOAD DISTINCT Canal, Zone , Date From Objective Data;

LinkTable:

LOAD AUTONUMBER(Canal&'-'&Zone&Date) AS %FACT_KEY ,

AUTONUMBER(Canal&'-'&Zone&Date) AS %OBJECTIVE_KEY ,

Date AS %CALENDER_KEY

Resident TempLinkTable;

DROP Table TempLinkTable ;

Based on your requirements you can keep Canal , Zone in the Link Table of fact table.

Re: Always show objective

Alejandro_Hernández — Thu, 13 Feb 2014 00:50:05 GMT

instead of joining try concatenating your goals and fact table..see script of attached example

Re: Re: Always show objective

fkeuroglian — Thu, 13 Feb 2014 01:28:56 GMT

People, i send an example

as you can see if you chosee any month year of 2014, the objective desapear

thank you again for your help!

Fernando

Re: Always show objective

Thu, 13 Feb 2014 03:58:02 GMT

If Objective data have date & you need to add the date field in your key