12 Replies Latest reply: Apr 26, 2012 1:34 AM by Bill Ringer Salalima RSS

    I hate looping issue of table structure

    Bill Ringer Salalima

      What should I do? I really need to make this work...

       

      I have tables,

       

      RR_Header

           rr_number

           date

       

      RR_Details

           rr_number

           item_code

           received_qty

       

      DR_Header

           dr_number

           date

       

      DR_Details

           dr_number

           item_code,

           delivered_qty

       

      THIS IS ONLY SIMPLE TABLES....

      RR_Header should link to RR_Details (rr_number)

      DR_Header should link to DR_Details (dr_number)

      RR_Details should link to RR_Details (item_code)

      RR_Header should link to DR_Header (date)

       

      So date I could have this report...

       

      DATEITEMDELIVEREDRECEIVED
      12-12-12A1015
      11-11-11B58

       

      but damn, i hate this thing... TABLE STRUCTURE LOOP... Whatever! It made my blood gets hot... Please help me about this...

       

      I really need to meet the deadline...

       

      I've attached the screenshot of the table structure...

        • Re: I hate looping issue of table structure
          Miguel Angel Baeyens de Arce

          Hi,

           

          This is one of the most powerful QlikView features and the foundation of Associative Logic. If you don't understand this right, you better take some courses (there are good ones for free in the QlikView portal, Training).

           

          Answering to your question, and in regards to the above: you need to rename the fields that are not the same values in different tables. For example, Customers may have a field named "Date" that is used to set the first purchase date, whilst Invoices may have another "Date" field that stores the date when the invoice was issued. Since they are not the same thing they must be renamed otherwise it will only lead to confusion and will return unexpected errors.

           

          So your example should look like this:

           

          Headers:
          LOAD rr_number AS LinkHeaderDetails, date, 'RR' as Type INLINE [
          rr_number, date
          ];
          Details:
          LOAD rr_number AS LinkHeaderDetails, item_code, received_qty INLINE [
          rr_number, item_code, received_qty
          ];
           
          DR_Header: // informative, not used
          CONCATENATE (Headers) LOAD dr_number AS LinkHeaderDetails, date INLINE [
          dr_number, date
          ];
           
          DR_Details: // informative, not used
          CONCATENATE (Details) LOAD dr_number AS LinkHeaderDetails, item_code, delivered_qty INLINE [
          dr_number, item_code, delivered_qty
          ];
          
          

           

          Hope that helps.

           

          Miguel

           

          EDIT: Check attached application. That's how a master calendar, fact table, dimension table and so on are built in their easiest and simplest way. You don't need to know datawarehouseing or datamining to get a QlikView application working, but you certainly do need to be familiar with these concepts. It's not only about avoiding loops, rather than creating working data models to return expected results in the best response time possible to help decision making process easier.

            • I hate looping issue of table structure
              Bill Ringer Salalima

              Hi Miguel,

               

              Thank you so much for sharing this wonderful and helpful answered prayer. God bless.

               

              Best Regards,

               

              Bill

                • Re: I hate looping issue of table structure
                  Miguel Angel Baeyens de Arce

                  Hi Bill,

                   

                  I'm glad to help. Bear in mind that, although QlikView is certainly easy to use, (or at least easier than oter tools) it all depends on what do you need to achieve. The QlikCommunity is the best resource of QlikView knowledge so far, with the largest number of users in the BI community. Most of them are real geniuses, and to mention a few, read John Witherspoon, Rob Wunderlich, Rakesh Mehta, Oleg Troyansky, Vlad Gutkovsky, Stephen Redmond, Ralf Becher, Jay Jakosky, Steve Dark, Gordon Savage, just to mention a few... There are more talented people of course, and is not unsual that I end up googling for "site:qlik.com iis configuration gutkovsky" for example because it's the fastest way to get a good answer to your problem, once you know which area of expertise belongs to whom.

                   

                  But when it takes to data modelling, it's not just QlikView that, as any other software has its own "rules" (functions, syntax, you name it) rather than data modelling as you need to know to create a database or a datawarehouse (snowflake and star schemas, link tables, key fields, dimensions, fact tables...).

                   

                  Fortunately, there is no "best answer" in most cases, and you can get to the same answer following different paths, not always being one of them better than the other, but there do are some good practices (usually based on the experience strengthened by your skills) that lead to create good applications both visual and performing.

                   

                  Regards.

                   

                  Miguel

                    • I hate looping issue of table structure
                      Bill Ringer Salalima

                      Can you still help?

                       

                      I still have one problem... this is continuation to above, there's another table.

                       

                      SR_Header

                           sr_number

                           return date

                       

                      SR_Details

                           sr_number

                           returned_qty

                           ref_no

                       

                      This is to have this report

                       

                       

                      DateItemDate ReturnDeliveredReturn
                      12-12-12A12-15-121015
                      11-11-11B11-20-1258

                       

                      DR_Details (dr_number) should link to SR_Details (ref_no)

                       

                       

                       

                      I'm glad to be part of the forum..

                        • Re: I hate looping issue of table structure
                          Miguel Angel Baeyens de Arce

                          Hi Bill,

                           

                          One way to do it in the attached application. There are more for sure.

                           

                          Regards.

                           

                          Miguel

                            • I hate looping issue of table structure
                              Bill Ringer Salalima

                              What if the dr_number of DR_Details should be link to SR_Details ref_no

                               

                              Consider the following tables and data is related to above...

                               

                              Example:

                                  

                              DR Detail

                                   Item    dr_number

                                   A          DR001

                                   A          DR002

                                   B          DR001

                               

                              SR_Details

                                   sr_number     Item     ref_no      Returned Qty

                                      SR01            A         DR001               15

                                      SR02            B         DR001               8

                               

                              SR_Header

                                   sr_number     date return

                                   SR01               12-15-12

                                   SR02               11-20-12

                               

                              I need to have this report

                               

                              1)     DATE     ITEM     RETURN DATE     DELIVERED     RETURN

                              2)     DATE     ITEM     SELL OUT     SELL IN

                               

                              *Sell out = Delivered - Return

                              *Sell In = Received

                               

                              -I did what you have did w/ second qvw, I've notice the item has been link between DR Details and SR Details, could you make it DR Details (dr_number) and SR_Details (ref_no) ?

                                • I hate looping issue of table structure
                                  Bill Ringer Salalima

                                  Im sorry I got wrong link...

                                   

                                   

                                  DR_Details (dr_number)  should link to SR_Header ( ref_no )

                                   

                                  so this is the structure

                                   

                                  SR_Header

                                       sr_number     ref_no

                                    • I hate looping issue of table structure
                                      Bill Ringer Salalima

                                      Hi Miguel Angel Baeyens,

                                       

                                      Below are final structure. This is additional to first structure you have made in first Qlikview app.

                                       

                                      DR Detail

                                           Item    dr_number

                                           A          DR001

                                           A          DR002

                                           B          DR001

                                       

                                      SR_Details

                                           sr_number     Item         Returned Qty

                                              SR01            A                       15

                                              SR02            B                       8

                                       

                                      SR_Header

                                           sr_number     date return     ref no

                                           SR01               12-15-12          DR001

                                           SR02               11-20-12          DR002

                                       

                                      I need to have this report

                                       

                                      1)     DATE     ITEM     RETURN DATE     DELIVERED     RETURN

                                      2)     DATE     ITEM     SELL OUT     SELL IN

                                       

                                      *Sell out = Delivered - Return

                                      *Sell In = Received

                                        • Re: I hate looping issue of table structure
                                          Miguel Angel Baeyens de Arce

                                          Bill,

                                           

                                          The following should work. Note that these is not real data, so you will need to adapt or create more links between tables or modify the existing ones.

                                           

                                          Hope that helps.

                                           

                                          Miguel

                                            • I hate looping issue of table structure
                                              Bill Ringer Salalima

                                              Hi Mig,

                                               

                                              I will apply the latest one okay? Will inform once work!

                                               

                                              Regards,

                                              Bill

                                                • Re: I hate looping issue of table structure
                                                  Bill Ringer Salalima

                                                  Hi Mig,

                                                   

                                                  Have you check on this?

                                                   

                                                  What if the dr_number of DR_Details should be link to SR_Details ref_no

                                                   

                                                  Consider the following tables and data is related to above...

                                                   

                                                  Example:

                                                   

                                                  DR Detail

                                                       Item    dr_number

                                                       A          DR001

                                                       A          DR002

                                                       B          DR001

                                                   

                                                  SR_Details

                                                       sr_number     Item     ref_no      Returned Qty

                                                          SR01            A         DR001               15

                                                          SR02            B         DR001               8

                                                   

                                                  SR_Header

                                                       sr_number     date return

                                                       SR01               12-15-12

                                                       SR02               11-20-12

                                                   

                                                  I need to have this report

                                                   

                                                  1)     DATE     ITEM     RETURN DATE     DELIVERED     RETURN

                                                  2)     DATE     ITEM     SELL OUT     SELL IN

                                                   

                                                  *Sell out = Delivered - Return

                                                  *Sell In = Received

                                                   

                                                  -I did what you have did w/ second qvw, I've notice the item has been link between DR Details and SR Details, could you make it DR Details (dr_number) and SR_Header (ref_no) ?

                                                   

                                                  -Im sorry I forgot to tell you that the ref_no is not in SR_Details... it is in SR_Header...

                                                   

                                                  The link of SR_Header to DR_Details is below

                                                       SR_Header

                                                            ref_no as dr_number

                                                   

                                                       SR_Details

                                                            dr_number

                                                   

                                                  something like that... Hehe

                                                • Re: I hate looping issue of table structure
                                                  Bill Ringer Salalima

                                                  Hi Mig,

                                                   

                                                       Thanks for the help. I come up this structure. Thanks for the ideas. Got knowledge. Hehe

                                                  tablestructure.png

                                                   

                                                  Best Regards,

                                                  Bill