4 Replies Latest reply: Apr 10, 2016 6:56 AM by Stefan Wühl RSS

    Allocating Order Level Information to Line level

    Shan Ahmed

      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

        • Re: Allocating Order Level Information to Line level
          Karla Rivas

          Hi Ahmed100,

           

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

           

          Best Regards,

           

          -Karla

          • Re: Allocating Order Level Information to Line level
            Stefan Wühl

            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.

            • Re: Allocating Order Level Information to Line level
              Shan Ahmed

              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

                • Re: Allocating Order Level Information to Line level
                  Stefan Wühl

                  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