topic Re: Mapping multiple fields of multiple tables and finding matching value in QlikView

Mapping multiple fields of multiple tables and finding matching value

Anonymous — Tue, 07 Feb 2017 13:10:40 GMT

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

Re: Mapping multiple fields of multiple tables and finding matching value

Anonymous — Tue, 07 Feb 2017 14:25:27 GMT

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

Re: Mapping multiple fields of multiple tables and finding matching value

Anonymous — Wed, 08 Feb 2017 04:40:35 GMT

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

Re: Mapping multiple fields of multiple tables and finding matching value

sdmech81 — Wed, 08 Feb 2017 05:37:13 GMT

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.

Re: Mapping multiple fields of multiple tables and finding matching value

Anonymous — Wed, 08 Feb 2017 05:41:46 GMT

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

Re: Mapping multiple fields of multiple tables and finding matching value

rahulpawarb — Wed, 08 Feb 2017 05:47:50 GMT

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

Re: Mapping multiple fields of multiple tables and finding matching value

sdmech81 — Wed, 08 Feb 2017 05:52:05 GMT

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

Re: Mapping multiple fields of multiple tables and finding matching value

Anonymous — Wed, 08 Feb 2017 11:07:28 GMT

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

Re: Mapping multiple fields of multiple tables and finding matching value

sdmech81 — Wed, 08 Feb 2017 12:09:03 GMT

Is this done?

Sachin

Re: Mapping multiple fields of multiple tables and finding matching value

rahulpawarb — Wed, 08 Feb 2017 12:15:49 GMT

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

Re: Mapping multiple fields of multiple tables and finding matching value

Anonymous — Thu, 09 Feb 2017 04:18:03 GMT

Hi Sachin,

Thanks for reply. Yesterday i was bit busy with other projects, Today i will try this and update you.

Regards,

Krishna

Re: Mapping multiple fields of multiple tables and finding matching value

Anonymous — Thu, 09 Feb 2017 06:54:50 GMT

Hi Rahul,

I have to tables as below

Table 1
Col1	Col2	Col3
A	1	200
B	3	100
C	4	400
D	5	500
A	2	500
D	3	300
C	5	600

Table 2
Col1	Col2
A	1
D	5
D	3
C	4

Could you suggest me how could i do look up or any other way to get COL3 value of table 1 on matching with both column values of table 2?

Output should be:

Table 3
Col1	Col2	Col3
A	1	200
D	5	500
D	3	300
C	4	400

Thanks to advice.

Regards,

Krishna

Re: Mapping multiple fields of multiple tables and finding matching value

Anonymous — Thu, 09 Feb 2017 06:55:54 GMT

Hi Sachin,

Now data model is changed and i am trying to get solution for below scenario. Thanks to advice.

Hi Rahul,

I have to tables as below

Table 1
Col1	Col2	Col3
A	1	200
B	3	100
C	4	400
D	5	500
A	2	500
D	3	300
C	5	600

Table 2
Col1	Col2
A	1
D	5
D	3
C	4

Could you suggest me how could i do look up or any other way to get COL3 value of table 1 on matching with both column values of table 2?

Output should be:

Table 3
Col1	Col2	Col3
A	1	200
D	5	500
D	3	300
C	4	400

Thanks to advice.

Regards,

Krishna

Re: Mapping multiple fields of multiple tables and finding matching value

rahulpawarb — Thu, 09 Feb 2017 07:21:41 GMT

Hello Krishna,

You can me use of below given sample script to create desired output (Final Data Model will contain only Table2; if you want Table1 for further use then create Mapping table using resident load - Refer commented code in the script).

Table1:
Mapping LOAD 
AutoNumber(Col1 & '|' & Col2, 'Key') AS Key,
Col3;
LOAD * INLINE [
Col1, Col2, Col3
A, 1, 200
B, 3, 100
C, 4, 400
D, 5, 500
A, 2, 500
D, 3, 300
C, 5, 600
];


Table2:
LOAD *,
     ApplyMap('Table1', AutoNumber(Col1 & '|' & Col2, 'Key')) AS Col3;
LOAD * INLINE [
Col1, Col2
A, 1
D, 5
D, 3
C, 4
];

Refer the attached sample application.

Thank you!

Rahul

Re: Mapping multiple fields of multiple tables and finding matching value

Anonymous — Thu, 09 Feb 2017 10:12:57 GMT

Hi Rahul,

Thanks Alot. But final one question. Is it possible to mention some default value if match does not find.

Regards,

Krishna

Re: Mapping multiple fields of multiple tables and finding matching value

rahulpawarb — Thu, 09 Feb 2017 10:26:52 GMT

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

Re: Mapping multiple fields of multiple tables and finding matching value

Anonymous — Thu, 09 Feb 2017 10:44:02 GMT

Thank You Rahul.

Re: Mapping multiple fields of multiple tables and finding matching value

rahulpawarb — Thu, 09 Feb 2017 10:53:42 GMT

Cheers,

Rahul