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

    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

        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

            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

                    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