topic Re: Mapping Question in QlikView

Mapping Question

hmjason7 — Mon, 29 Jun 2015 09:02:41 GMT

Hi All,

I have a data modelling question. I have 2 tables: a standard Transactions table, and a Customer Table.

The transaction table looks like:

Customer ID | Transaction Date

123 | 01-01-2015

123 | 15-02-2015

123 | 24-04-2015

The Customer Table looks like this: (where the data is updated on a monthly basis for the beginning of the month. The CustomerID can be reused, hence why for April 2015 the Customer ID 123 has been changed to Samsung).

Customer ID | Customer Name | Date

123 | CocaCola | 01-01-2015

123 | CocaCola | 01-02-2015

123 | CocaCola | 01-03-2015

123 | Samsung | 01-04-2015

123 | Samsung | 01-05-2015

What is the best way of mapping this Customer data into the Transaction table, so I can get something like:

FactTable:

Customer ID | Customer Name | Transaction Date

123 | CocaCola | 01-01-2015

123 | CocaCola | 15-02-2015

123 | Samsung | 24-04-2015

Cheers,

Jason

Re: Mapping Question

Anonymous — Mon, 29 Jun 2015 09:08:51 GMT

Hi Jason,

If you always update customer names on the first of the month and there's always a record for a customer ID in the customer table each month you should be able to do a composite key based on:

Transaction side:

[Customer ID] & MonthStart(Transaction Date) as %CustomerKey

Customer side:

[Customer ID] & MonthStart(Date) as %CustomerKey

Re: Mapping Question

buzzy996 — Mon, 29 Jun 2015 09:12:06 GMT

tis case u can try ,transaction table.Transaction Date month and year equal to Customer Table.Date month and year,if so then get cust id from Customer Table.

but this is not a ultimate solution,some time sit will mismatch with other data.

Re: Mapping Question

Mon, 29 Jun 2015 09:29:30 GMT

Hi,

A Composite key with Apply Map Function can be helpful.

Create a composite Key of [Transaction Date & Customer ID] from Transaction Table.

Use it to match the [ Date & Customer ID] from Customer Table and fetch the Customer Name to create the Fact Table.

Hope it helps !

Cheers.

Re: Mapping Question

hmjason7 — Mon, 29 Jun 2015 09:49:17 GMT

Yes you're right - thanks

I was just doing a test using an inline table.

Re: Mapping Question

Anonymous — Mon, 29 Jun 2015 09:59:07 GMT

Hi Jason,

you can concatenate customerID & month & year of the both date and use this for tha mapping.

I attach a example, with load inline I should use temporary tables, you can use preciding load of preciding load maybe.

Reagrds.

Re: Mapping Question

timanshu — Mon, 29 Jun 2015 10:08:32 GMT

Hi,

Make Composite key:

Transaction Table:

Customer ID & '- ' & Month(Transaction Date) & '- ' & Year(Transaction Date) as Key;

Customer Table:

Customer ID & '- ' & Month(Date) & '- ' & Year(Date) as Key;