Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
imrasyed
Partner - Creator II
Partner - Creator II

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
Specialist
Specialist

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.

View solution in original post

12 Replies
Quy_Nguyen
Specialist
Specialist

Hi,

Could you please share your data model/script?

imrasyed
Partner - Creator II
Partner - Creator II
Author

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
Creator
Creator

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

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

agigliotti
Partner - Champion
Partner - Champion

what are your chart dimensions and measures ?

I can't see your calendar script.

imrasyed
Partner - Creator II
Partner - Creator II
Author

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
Specialist
Specialist

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
Partner - Creator II
Partner - Creator II

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
Contributor III
Contributor III

May I know why are you using Autonumber?

imrasyed
Partner - Creator II
Partner - Creator II
Author

Yes I am using keydate in my calendar table