Allocating Order Level Information to Line level

[AH]

Hi,

What is the best way to allocate header level Invoice ID to Item Level? Also I want to allocate some of the other Field from the Header level to Item level.

I would really appreciate the feedback.

Thanks,

ahmed100

[Anonymous]

Hi Ahmed100,

Could you please provide a very simple example about how your header and item data is structured.

Best Regards,

-Karla

[swuehl]

Could you elaborate further on you allocation requirements?

In general, you should take care not to duplicate fact values on aggregation, e.g. by when just joining header to line item level.

So it's sometime best to just keep these two tables as separate tables, linked by the header key.

You can also look into

Fact Table with Mixed Granularity

for an approach using a single fact table.

[AH]

Hi Karla & swuehl,

Please find attach excel file of an example of what i am trying to achieve. In the excel file i have a header tab with a simple transaction with couple of fields. And in the Item tab i have the item level details of the transaction happened in the header/order level.

What i would like to do is, get all the fields from Header level and allocate them in the item level and make a unified fact table. I think Inner joining the Item table with Header table would do the trick but both table in real application are having more than 5 million records and inner joining is freezing qlikview in my desktop.

What would be the best way to allocate the necessary fields from Header table to Item table and make a unified Fact table? Also, please let me know if  there is any best practice in this situation.

Thanks,

ahmed100

[swuehl · Accepted solution]

ahmed100,

a good data model should support the analysis you want to do, so it's not easy to tell which is the best solution.

I think you have multiple options:

1) Leave the tables as they are, linked by InvoiceID

2) Bring in each header line next to the line item level on the same record

- JOIN the tables (you would need to investigate why this freezes your computer. BTW. are both tables really similar in size, approx. 5 mill rows?)

- ApplyMap()  the header line information to the line item level

To Join or not to Join

Don't join - use Applymap instead

- There is also a Lookup() function you can look into

3) Concatenate your two tables using CONCATENATE LOAD prefix.

This will bring in your facts not on the same record, but on different records. Compared to 2) , this will not duplicate your header facts per line item.

You can look into the mixed fact table blog post I linked in my previous post.

If you make selections in fields that belong only to header or line item, the other group's records will be excluded by default, so you would need to use set analysis to access these records.

It's up to you and your requirements which approach is best suited, if unsure, try to play around with the scenarios to get a feeling about performance etc. with your data and requested charts.

Hope this helps,

Stefan