topic Re: Setting up Evolving Dimension in QlikView

Setting up Evolving Dimension

Anonymous — Mon, 30 Nov 2015 17:11:02 GMT

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,

Re: Setting up Evolving Dimension

JonnyPoole — Mon, 30 Nov 2015 17:19:35 GMT

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.

Re: Setting up Evolving Dimension

Anonymous — Mon, 30 Nov 2015 22:08:08 GMT

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?

Re: Setting up Evolving Dimension

satishkurra — Mon, 30 Nov 2015 22:11:02 GMT

Check this for more information

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

Thanks

Satish

Re: Setting up Evolving Dimension

JonnyPoole — Tue, 01 Dec 2015 13:42:35 GMT

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

];

Re: Setting up Evolving Dimension

Anonymous — Tue, 01 Dec 2015 20:18:04 GMT

Thank you, this helps a lot.