Setting up Evolving Dimension

[Anonymous]

Hello All,

I'm new to Qlikview and wondering what the best practice would be for an evolving dimension. I've looked over some white papers, but I'm wondering if anyone could provide some advice based on my specific circumstances.

I work in the Auto industry for an OEM and much of my data is attached to a Dealer Code. The issue I have is that often the codes change and I need a way to link the old data to the new.

For instance if I have a Dealer Code 'A123' that changes to 'A124', is there a way to have Qlikview reference my table (Vlookup?) of Dealer Codes so that all of the old data that was associated with "A123" is now associated with "A124"?

Could I use Active/Inactive dates to achieve this?

Thank you for any assistance that you can provide.

BR,

DJ

[JonnyPoole]

You can . The extended syntax of the "interval match" capability allows you to join a fact table (with 1 date) with a dimension table (that has start/end) date with a key value like product.

[Anonymous]

Thank you Jonathan. I still don't understand  how to associate the new data to the old. In other words, I will have data files that show "A123", however I want that to be attributed to "A124".

Maybe I'm thinking about this incorrectly, it seems that IntervalMatch would only match the Number to the time Interval. I also need to associate my data from the old number to the new. Can I do both?

[satishkurra]

Check this for more information

http://www.learnqlickview.com/intervalmatch-function-and-slowly-changing-dimensions/

Thanks

Satish

[JonnyPoole · Accepted solution]

You would need to maintain the current mapping. Here is an example .

Data:

load * inline [

Code,Date

A123,6/1/2015

A124,6/1/2014

B120,6/1/2015

B119,6/1/2014

B118,6/1/2013

];

Inner Join IntervalMatch (Date,Code)

LOAD  * inline [

StartDate,EndDate,Code

1/1/2015,,A123

1/1/2014,12/31/2015,A124

1/1/2013,12/31/2014,B118

1/1/2014,12/31/2014,B119

1/1/2015,12/31/2015,B120

];

HistoricalCodeGroups:

load * inline [

Code,CurrentCode

A124,A123

A123,A123

B118,B120

B119,B120

B120,B120

];

[Anonymous]

Thank you, this helps a lot.