Product Hierarchy based on the Date

[jasonreynaldo]

Good Evening, i want to ask about Hierarchy ini Qlikview,

Case:

I have 2 type of product hierarchy :

1. Old Product Hierarchy  that i define the start_date_history on '01/01/2000' and end_date_history on  '31/12/2018'

Old Prod Hier have 3 Levels of Hierarchy;

2. New Product Hierarchy  that i define the start_date on '01/01/2019' and end_date on  '31/12/2999'

New Prod Hier have 10 Levels of Hierarchy ;

so in this case those 2 different  Old Prod Hier and New Prod Hier , i combine those 2 and, store into PRODUCT_HIERARCHY.QVD(qvd),

after that i want to connect those dates that i defined with company transactional dates, without using any interval match, 

my concern is, if i have a large amount of product hierarchy data, then what is the best practice to reach the best optimization to load the data ?

My final expectation for the result is, when i choose the date on 12-12-2018, it will associate with the Old Product Hierarchy, and when choose the date on 12-01-2019,  it will associate with the New Product Hierarchy.

Appreciate to any of your solutions.

Thank you

[pedrobergo · Accepted solution]

Hi Jason,

The experience says that you have to try to obtain better solution.

But, thinking about your needs, you can use these two options:

1. Create a Key in the fact table to dimension hierarchy wich contains the date. In this case, you must hardcode to determine the date inside the key. It´s a good option for better performance.

2. Use intervalmatch() to create a bridge table that connect the fact table wich the dimension hierarchy. It´s not so good for performance, but it´s better if you can´t hardcode the date in the key.

There is another options, but it´s a start,

Pedro

[jasonreynaldo]

Dear Pedro,
Thank you for your help, i tried your option number 2 but the data increase too much, i didn't know why interval match did that, so i avoid to use it. Maybe you can help me with the 3rd option ?
Thank you ,
Jason

[pedrobergo]

Hi Jason,

I made an app wich contais a 3rd option, its derivate from IntervalMatch and create a Concatenate Key in the FactTable to connect a ProductHierarchy Table, so you don´t need a BridgeTable and the performance remains good.

See the app and tell if you can use it,

Pedro