4 Replies Latest reply: Jan 24, 2012 3:37 AM by babymand RSS

    Multiple lookup or join tables during load

      I  am a relative  QV n00b, i've searched the forums and google far and wide, but perhaps I am not knowledgeable enough to know what terms to search for.

       

      I'll quickly describe the issue I find and perhaps someone can point me in the right directions.

       

      I have 2 main tables.

       

      1 Master table with CRM data.  ( approix 200k lines ) (CRM:)

       

      1 Supporting table with invoice data.  (INVOICE:)  ( in excess of 3 million lines )

       

       

      I need to match invoice data back to CRM data, which works fine for 95% , it's done on a combined key with CRMkey + Partnumber.  in both tables.

       

      however:  the remaining 5% is an anomaly created by historic data, where it was possible to use a model name instead of a partnumber.

       

      So I need to match the data twice..

       

      once from CRM on PN to INVOICE which gives me 95%

      then once from CRM on MODEL to INVOICE which is supposed to give me the remaining 5% ,  but since this is another column in INVOICE I seem to get stuck with syn links which I could do without, or duplication.

       

      I was thinking about loading the invoice table once, storing it into a QVD and then trying to match and join, I think i'm on the correct route, but I just can't seem to get my head around it.. I hope someone can give me a boost!

        • Multiple lookup or join tables during load
          Karl Pover

          If you can map the model name to the part number, I would recommend creating a map table (look for "Mapping Load") and then apply the map (look for applymap()) to the field.  You would need to identify which CRM records are by PN and which are by Model and you would have the 1 key instead of 2.  For example,

           

          PN_Model_Map:

          Mapping

          Load PartNumber,

               Model

          From ...;

           

          Load CRMkey & '_' &

               if(CRMDate<=makedate(2008,1,1), applymap('PN_Model_Map',PartNumber), PartNumber) as InvoiceKey,

              *

          From ...;

           

          Or if you don't need a mapping just do the following:

           

          Load CRMkey & '_' &

               if(CRMDate<=makedate(2008,1,1), Model, PartNumber) as InvoiceKey,

              *

          From ...;

           

           

          Finally, if you don't know which invoices or CRM records contain which key, I would do something like what you are thinking.  I would load one of the tables and do an inner join with the other table with the PN as the key and then concatenate that table with an inner join of the same 2 tables with the model as key.  The 2 keys will have the same field name.  At the end if not all CRM records have invoices and not all invoices have CRM records you'll  have to concatenate the CRM records and Invoices that don't exist in the joined table.  You could use the exists() function to load records from the 2 tables and don't exist in the joined table.

           

          I hope that gives you some direction.

           

          Karl

            • Multiple lookup or join tables during load

              Thanks Karl,


              I will have to try your final solution, because indeed I don't know which fields have an invoice, and which don't.

               

              CRM is my main table, the invoices come from a huge database which would not like to load twice from it's source, so I guess I could use resident or QVD?

               

              I'm going to give this a whirl and come back with succes or fail.   Thanks much in advance!

              • Multiple lookup or join tables during load

                Once more. I've forgot to add that the last 3 digits of MODEL are always the same.

                 

                so a PN would be 7 Characters (numeric or alpha)  but a MODEL is always 4x Numeric and ending with ABC.

                 

                If I load CRM with the IF

                 

                if(right(PNCRM,3)="ABC", Model, PN) as InvoiceKey,

                 

                Where Model keys against Model in INVOICE and  PN against PN in INVOICE.

                Will Qlikview create the SynLinks because we are matching 2 keys in one load?

                  • Re: Multiple lookup or join tables during load

                    Final ( Working ) model (simplified)

                     

                     

                    INVOICE:
                    LOAD PN,
                         MTM,
                         QTYSM
                    
                    
                    FROM
                    [Book1.xlsx]
                    (ooxml, embedded labels, table is INVOICE);
                    
                    
                    //FIRST LOAD
                    CRM1:
                    inner join (INVOICE)
                    LOAD PNCRM as PN,
                                   QTY
                    
                    FROM
                    [Book1.xlsx]
                    (ooxml, embedded labels, table is CRM);
                    
                    
                    
                    
                    INVOICE1:
                    NoConcatenate
                    LOAD PN,
                         MTM,
                         QTYSM
                    
                    
                    FROM
                    [Book1.xlsx]
                    (ooxml, embedded labels, table is INVOICE);
                    
                    
                    //SECOND LOAD
                    CRM2:
                    inner join (INVOICE1)
                    LOAD PNCRM as MTM,
                                   QTY
                    
                    FROM
                    [Book1.xlsx]
                    (ooxml, embedded labels, table is CRM);
                    
                    
                    Final:
                    Concatenate (INVOICE)
                    Load
                    PN,
                    QTYSM,
                    QTY,
                    MTM
                    resident INVOICE1;
                    
                    
                    drop table INVOICE1;