Mapping multiple fields of multiple tables and finding matching value

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[sdmech81]

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.

[Anonymous]

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

[rahulpawarb]

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

[sdmech81]

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

[Anonymous]

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

[sdmech81]

Is this done?

Sachin

[rahulpawarb]

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