Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

imrasyed
Contributor

Link to fact Table

Hi Experts,

I have Sales fact and Inventory Fact.

I have concatenated both the facts and I have linked sales table to Calendar table.

When i select the year sales measure are matching but inventory not matching.

Even after formatting the date column in inventory table it is still not linking with calendar.

Please can anyone let me know what could be the problem?

Really appreciate u r quick help.

1 Solution

Accepted Solutions
Quy_Nguyen
Contributor III

Re: Link to fact Table

You said that your Calendar is from Database direct table. So the number of keydate is fixed right?

It could be the problem here because you can only connect autonumber keys that have been generated in the same data load.

12 Replies
Quy_Nguyen
Contributor III

Re: Link to fact Table

Hi,

Could you please share your data model/script?

imrasyed
Contributor

Re: Link to fact Table

Sales:

LOAD

    TERRITORY_NO,

    PERIOD_DATE,

    INVOICE_DATE,

    PRODUCT_NO,

    DIVISION_ID,

    ORDER_TYPE,

    UNIT_SALES,

    PRODUCT_LINE_ID,

    Autonumber(Num(date(date#(INVOICE_DATE,'YYYYMMDD'),'YYYYMMDD'))) as keydate

FROM    Sales.QVD]

(qvd)

where Year(Date(date#(PERIOD_DATE,'YYYYMM'),'YYYYMM'))>= year(AddYears(Today(),-2))

and    match(ORDER_TYPE,'SD','SO','SC') 

      AND match(DIVISION_ID,1,3)       

      and (TERRITORY_NO>=100 and TERRITORY_NO<=299)

      and not match(TERRITORY_NO,190)

      and not match(PRODUCT_LINE_ID,6);


concatenate(Sales)


Inventory:


LOAD

    TERRITORY_NO,

    REP_ID,

    PRODUCT_NO,

    SUB_PRODUCT_LINE_ID,

    PRODUCT_LINE_ID,

    WAREHOUSE_CODE,

    UNIT_INVENTORY,

    LIST_PRICE_PER,

    COST_PER_CURRENT_YEAR,

    COST_PER_PRIOR_YEAR,

    BUSINESS_UNIT,

    FAMILY,

    SUB_FAMILY,

    DIVISION_ID,

    AREA_MANAGER_ID,

    END_OF_MONTH_DATE,

    Autonumber(Num(Date(Floor(END_OF_MONTH_DATE),'YYYYMMDD'))) as keydate,

    SFTYSK

FROM      NVENTORY_HISTORY.QVD]

(qvd);



Please have a look at the script above.

END_OF_MONTH_DATE from inventory is in d/m/yyyy Timestamp  format so i have changed it to YYYYMMDD which is the format of INVOICE_DATE from sales and Calendar Date.



narayanamsn
New Contributor III

Re: Link to fact Table

you may think about Link Table concept (Multiple Fact Tables)

http://www.qlikfix.com/2016/09/08/elegant-link-table-script/

agigliotti
Honored Contributor II

Re: Link to fact Table

what are your chart dimensions and measures ?

I can't see your calendar script.

imrasyed
Contributor

Re: Link to fact Table

Dimensions  :Territory No,Product No,

Measures  :  Sum(Unit_sales) ,, Sum(Unit_Inventory)

Calendar Script : For now you can ignore and consider Invoice date which will be joined to calendar.

And calendar  is actually  from Database direct table and i am not creating a seperate one.

Quy_Nguyen
Contributor III

Re: Link to fact Table

I think you have to use keydate in your concatenate result table to create the Calendar. How could you make sure that the Calendar from database completely link with your data table?

ruanhaese
Contributor

Re: Link to fact Table

As Quy pointed out, there is could be linking issue with

using the Autonumber function on different fact tables which

probably dont correspond with the the values in the Calendar table.

Try removing Autonumber to make sure all the values align.

amijit_hazarika
New Contributor III

Re: Link to fact Table

May I know why are you using Autonumber?

imrasyed
Contributor

Re: Link to fact Table

Yes I am using keydate in my calendar table

Community Browser