Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have FACTS table with date key & customer key. Two different Dimension tables customer table with customer key and time table with date key and respective month. Another dimension table with targets, month and customer area Key.
Now i want to load target value into facts table by mapping month value from facts with dimension target table field month and customer area filed of customer dimension with customer key.
Table Facts:
Datekey,
Customer key
Table Customer Dimension:
Customer key,
Customer Area
Table Target Dimension:
CustomerArea,
Month,
Target
Output i required is:
Table Facts:
DateKey,
CustomerKey,
month from datekey,customer area(Customerkey from Table CustomerDimension) and get Target of 'Table Target Dimension' as Target.
Could some one suggest me about data model or how to get target field into Facts table?
Thanks to sugget.
Regards,
Krishna Chaitanya B
Hello Krishna,
Yes we can pass default value. Please refer below given syntax and example (If no match found then -999 will assigned to Col3).
//Syntax: ApplyMap('MappingTableName', MappingColumn, DefaultValue) AS ColumnName
ApplyMap('Table1', AutoNumber(Col1 & '|' & Col2, 'Key'), -999) AS Col3
Hope this will be of help.
Regards!
Rahul
Hi Krishna,
Because you have The target on CustomerArea you can not link with Fact table which is detailed at CustomerKey level. You are not able to split the target from the beginning on CustomerKey?
Because your Output will duplicate the target on each Customer. Or you can split the target based on number of customers of a CustomerArea and then you can easy concatenate or join with the fact table.
Best regards,
Cosmina
Hi Cosmina,
I want target to be same for customerkey which is same as customer area.
Ex:
Customer Area Code and Target
A, 5000
B,2000
Customer Key
1 A 5000
2 B 2000
3 A 5000
4 B 2000
5 B 2000
6 A 5000
Target per order is duplicated as well, it is fine.
Regards,
Krishna
Hi,
Why do want to merge dimension in to fact table
I would like to give better idea here so tht QLik resources are consumed less.
Using Look up functions you can merge two tables Table Customer Dimension and Table Target Dimension.Say new table upon merging 2 is Customer
And have only distinct customer Key in that table
Upon that U have association of New customer table with ur fact table i.e Table Facts based on customer key.:)
Hope this helps.
Hi Sachin,
Yes, that was the first idea i had, But i have targets by month for each customer area. So i thought to duplicate target in Facts table.
Regards,
Krishna
Hello Krishna,
You can make use of below given sample code snippet (If there is change in data grain you have slightly modify the code):
[Table Facts]:
LOAD
DateKey,
CustomerKey,
MONTH(DateKey) AS MonthValue
FROM [Table Facts];
JOIN
[Table Customer Dimension]:
LOAD
CustomerKey,
CustomerArea
FROM [Table Customer Dimension];
JOIN
[Table Target Dimension]:
LOAD
CustomerArea,
Month,
Target
FROM [Table Target Dimension];
Regards!
Rahul
Hi,
I that case aftr merging above said 2 tables have distinct based on customer key and month.
Then have the association to fact based on combined key of customer key and month.
For this u create one key in ur fact n say customer+month
Sachin
Hi Rahul,
When i run Below Script
Sales:
LOAD *,
Month(DateKey) as TargetMonth;
SQL
SELECT
DELIVERY_DATE AS DateKey,
INVOICED_CUSTOMER AS CustomerKey
FROM dbo.FACT_DAILY_DATA WITH (NOLOCK)
WHERE DELIVERY_DATE>=20160101;
Concatenate
LOAD *,
Month(DateKey) as TargetMonth;
SQL
SELECT
INVOICE_DATE AS DateKey,
INVOICED_CUSTOMER AS CustomerKey
FROM dbo.FACT_MONTHLY_DATA WITH (NOLOCK)
WHERE INVOICE_DATE>=20160606;
JOIN(Sales)
[Customer]:
LOAD
CustomerKey,
CustomerRepName as TargetRepName
Resident Customer;
JOIN(Sales)
[Area Targets]:
CrossTable(TargetMonth, Targets)
LOAD [Sales Area] as TargetRepName,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM
(ooxml, embedded labels, table is Sheet1);
below Error I get and Target also not loaded into sales table.
Illegal combination of prefixes
JOIN(Sales)
[Area Targets]:
CrossTable(CalendarMonth, Targets)
LOAD [Sales Area] as CustomerRepName,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM
(ooxml, embedded labels, table is Sheet1)
Thanks to advice.
Regards,
Krishna
Is this done?
Sachin
Hello Krishna,
I believe that joining cross table with previous table is causing this issue. Could you please share the application with sample data? This will help us to do the root cause analysis.
Regards!
Rahul