Mapping Question

[hmjason7]

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

[Anonymous]

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

[buzzy996]

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.

[#4 · Accepted solution]

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.

[hmjason7]

Yes you're right - thanks

I was just doing a test using an inline table.

[Anonymous]

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.

[timanshu]

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;