Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
jasonreynaldo
Contributor II
Contributor II

Product Hierarchy based on the Date

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

Labels (3)
1 Solution

Accepted Solutions
pedrobergo
Employee
Employee

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

View solution in original post

3 Replies
pedrobergo
Employee
Employee

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

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
Employee
Employee

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