Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Hi,
Could you please share your data model/script?
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.
you may think about Link Table concept (Multiple Fact Tables)
http://www.qlikfix.com/2016/09/08/elegant-link-table-script/
what are your chart dimensions and measures ?
I can't see your calendar script.
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.
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?
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.
May I know why are you using Autonumber?
Yes I am using keydate in my calendar table