Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
hmjason7
New Contributor II

Mapping Question

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

1 Solution

Accepted Solutions
Not applicable

Re: Mapping Question

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.

View solution in original post

6 Replies
jsn
Honored Contributor

Re: Mapping Question

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
Honored Contributor II

Re: Mapping Question

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.

Not applicable

Re: Mapping Question

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.

View solution in original post

hmjason7
New Contributor II

Re: Mapping Question

Yes you're right - thanks

I was just doing a test using an inline table.

ggallina
Contributor III

Re: Mapping Question

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
Contributor III

Re: Mapping Question

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;