18 Replies Latest reply: Aug 4, 2011 2:54 AM by Patrick Laredo RSS

    Help Using Nulls and Joins

    Richard Sheppard

      Hi,

       

      I have 2 tables, Spend and Contracted:  first details the supplier and their spend, the second has the supplier and if they are a contracted supplier (Y).

       

      The Spend table contains more suppliers than can be found in the Contracted table

       

       

      I would like to combine the details found in the "Contracted" table back into the "Spend", and for those suppliers that are not listed in the "Contracted" table, to return "No" by default

       

      contracted.gif

       

      In the table above, I would like suppliers B, D, G, H, I and J all to return "No" under the "ContractedSupplier" field - but for some reason, my script is not working.  Anyone with any thoughts?

       

      Kind regards,

       

      Rich

        • Re: Help Using Nulls and Joins
          Miguel Angel Baeyens de Arce

          Hello Rich,

           

          Check the following script. I've removed the precedent load and it's loading all records in one table, using a two step load instead.

           

          Spend:
          LOAD * INLINE [
              Supplier, Spend
              A, 123
              B, 3132
              C, 243
              D, 345
              E, 345
              F, 345
              G, 435
              H, 345
              I, 435
              J, 345
          ];
          
          ContractedTable:
          LOAD * INLINE [
              Supplier, Contracted
              A, Y
              E, Y
              C, Y
              F, Y
          ];
          
          JOIN (Spend) LOAD Supplier,
              Contracted as ContractedX
          RESIDENT ContractedTable;
          
          FinalTable:
          LOAD Supplier,
               If(Len(ContractedX) = 0, 'N', ContractedX) AS Contracted,
               Spend
          RESIDENT Spend;
          
          DROP TABLES Spend, ContractedTable;
          

           

          Does this make sense to you?

           

          Miguel Angel Baeyens

          BI Consultant

          Comex Grupo Ibérica

            • Re: Help Using Nulls and Joins
              Richard Sheppard

              Hi Miguel,

               

              Thanks for this.  If i wanted to keep my "Spend" table, and include the "Y/N" contracted flag within the Spend table, how do I adjust this - ie my first thoughts were that this would have to be done with a preceding load - but I cannot get it to work...?

                • Re: Help Using Nulls and Joins
                  Miguel Angel Baeyens de Arce

                  Hi Rich,

                   

                  That preceding load will not work because the joined table doesn't exist yet (it will exist at the end of the load). Although you are doing here two steps, you are keeping all the information from the Spend table, and you can create and modify as many fields as you want.

                   

                  You can even rename the table to its original Spend name, if that suits you.

                   

                  Hope that helps.

                   

                  Miguel Angel Baeyens

                  BI Consultant

                  Comex Grupo Ibérica

                    • Help Using Nulls and Joins
                      Richard Sheppard

                      Hi Miguel,

                       

                      Thanks for your time.  Perhaps i need to clarify a bit further...   In my "real" document, I have 1 large fact table (Purchases), in which I would like to append the "Contracted" Y/N flag against.

                       

                      I have a separate table which identifies which suppliers are contracted (ie = Y).

                       

                      What I was wanting to achieve, was simply to join the "contracted" "Y" value to my "Purchases" table, and where no value has been returned, automatically set to "N".

                       

                      contracted2.gif

                       

                      As my "Purchases" table is already big / contains a lot of fields, do I therefore need to join all the remaining fields to the "FinalTable" as per your example, and then rename the table back to "Purchases"?

                        • Re: Help Using Nulls and Joins
                          John Witherspoon

                          How about a map with a default value?

                           

                          ContractedTable:
                          MAPPING LOAD * INLINE [
                              Supplier, Contracted
                              A, Y
                              E, Y
                              C, Y
                              F, Y
                          ];
                          Spend:
                          LOAD *,applymap('ContractedTable',Supplier,'N') as Contracted
                          INLINE [
                              Supplier, Spend
                              A, 123
                              B, 3132
                              C, 243
                              D, 345
                              E, 345
                              F, 345
                              G, 435
                              H, 345
                              I, 435
                              J, 345
                          ];

                            • Re: Help Using Nulls and Joins
                              Richard Sheppard

                              Hi John,

                               

                              Have tailored the above to my data, although I have an issue with the applymap statement, ie my full script runs and finishes only once I have commented out the latter part of the script (ie "Purchases' applymap")

                               

                              [code]

                              Contracted_Map:
                              Mapping load
                              Unique,
                              Contracted? as ContractedSupplier
                              resident ContractedSpend;


                              Purchases:
                              Load *,
                              applymap('Contracted_Map',Unique,'N') as ContractedSupplier
                              resident Purchases;

                               

                              [/code]

                               

                              do you have any thoughts?

                               

                              Kind regards,

                               

                              Rich

                                • Re: Help Using Nulls and Joins
                                  John Witherspoon

                                  Do the applymap() during the initial load of the purchases table, not after.  One point of this approach is to avoid doing an extra load of any kind.

                                    • Re: Help Using Nulls and Joins
                                      Richard Sheppard

                                      Hi John,

                                       

                                      Thanks for your reply! 

                                       

                                      At the moment, the bulk of the data that I am using is generated during the initial binary load, refering back to another QV document.

                                       

                                      Due to the above issue, would this now mean that I cannot use the applymap function on this table, or do you know of a workaround?

                                       

                                       

                                      Your comment re

                                      "One point of this approach is to avoid doing an extra load of any kind." - can you expand on this point, ie are you meaning that no additional load can be performed on the "Purchases" table, once I have used an "applymap" statement, anywhere in my script?  Or just during this immediate part of the script (ie up until the ";" (semi colon)?)

                                       

                                      Please advise,

                                       

                                      Kind regards,

                                       

                                      Rich

                                        • Re: Help Using Nulls and Joins
                                          John Witherspoon

                                          Ah, OK, if you're loading your tables with a binary load, that's a different animal.  I assume, then, that your contracted table is loaded during the binary load as well?  In that case, I'd probably use left joins.

                                           

                                          BINARY blah blah blah;

                                           

                                          LEFT JOIN (Spend)
                                          LOAD
                                          Supplier
                                          ,Contracted as ContractedTemp
                                          RESIDENT ContractedTable
                                          ;
                                          LEFT JOIN (Spend)
                                          LOAD
                                          the key field(s) to the spending table
                                          ,if(len(ContractedTemp),ContractedTemp,'N') as Contracted
                                          RESIDENT Spend
                                          ;

                                          DROP TABLE ContractedTable;

                                           

                                          What I meant by avoiding doing an extra load is that had you NOT been doing a binary load, you wouldn't have needed the left joins above.  There is nothing about doing an applymap() that prevents further loads.  It was a way to NOT NEED further loads.  You're basically doing two extra loads here, the two left joins.  Mind you, you probably get all that time back and more by doing the binary load, so I'm not suggesting you change your overall approach.  I merely misunderstood how you were loading your data. 

                                           

                                          The above solution is VERY similar to Miguel's, just with a left join at the end instead of loading a new table.  I believe that will take less RAM than creating a new table.  I'm not certain about CPU, but I'd guess about the same.

                                            • Re: Help Using Nulls and Joins
                                              Richard Sheppard

                                              Hi John,

                                               

                                              Thanks for the above! 

                                               

                                              My "Contracted Table" is something that I have loaded into the script (and therefore excluded from the binary load).  Does this then change the approach to be used?

                                               

                                              Have played with the above, and can get 2 new fields appended to my "Purchases" table, however, all data rows are currently returning a "Y", ie am unable to generate a suitable script that populates un-matched suppliers to "N"

                                               

                                              sample exert of my script:

                                              ____________________________________________________________

                                               

                                              Binary ".\Artemis_Qlikdatabase.qvw";       // loads the "Purchases" table from another qv doc

                                               

                                              ContractedSpend:

                                              LOAD zzContracted_Family,

                                              zzContracted_Class,

                                              zzContracted_SupplierID,

                                              Contracted?,

                                              Unique

                                              FROM [Contracted Spend - received.xls] (biff, embedded labels, table is UploadNew$);

                                               

                                               

                                              LEFT JOIN (Purchases)
                                              LOAD
                                              Unique,
                                              Contracted? as ContractedTemp
                                              RESIDENT ContractedSpend
                                              ;

                                              LEFT JOIN (Purchases)
                                              LOAD
                                              Unique,
                                              if(len(Contracted?),Contracted?,'N') as Contracted
                                              RESIDENT ContractedSpend
                                              ;

                                              DROP TABLE ContractedSpend;

                                               

                                              ____________________________________________________________

                                               

                                              have played around with:

                                               

                                              " if(Contracted?='Y','Y','N') as Contracted "

                                              " if(len(Contracted?)=1,'Y','N') as Contracted "

                                              " if(len(Contracted?)=1,Contracted?,'N') as Contracted "

                                               

                                              When inserting "ContractedTemp" instead of "Contracted?", QV returns a "field not found" error, which I guess is linked to the fact that the field has not yet been read/loaded into QV.

                                               

                                              but guessing the problem lies else where, as "technically" the above statements are correct...?

                                               

                                              Any thoughts, greatly appreciated,

                                               

                                              Kind regards,


                                              Rich

                                                • Re: Help Using Nulls and Joins
                                                  Patrick Laredo

                                                  hi,

                                                   

                                                  I think you have misread John's post.

                                                   

                                                  The first left join is from your table with contracted spend data,

                                                  LEFT JOIN (Spend)

                                                  LOAD

                                                  Supplier

                                                  ,Contracted as ContractedTemp

                                                  RESIDENT ContractedTable

                                                  ;

                                                   

                                                  this creates the new Field ContractedTemp wherever there is contracted spend.

                                                   

                                                  The second however is a left join of the spend table to itself.

                                                   

                                                  LEFT JOIN (Spend)

                                                  LOAD

                                                  the key field(s) to the spending table

                                                  ,if(len(ContractedTemp),ContractedTemp,'N') as Contracted

                                                  RESIDENT Spend

                                                   

                                                  This tests the new field - ContractedTemp. If it exists, fine, it places the information into a new field called Contracted, if not, ie in the case of your spend being non contracted, it puts the value 'N' into the contracted field.

                                                   

                                                  you will have to be careful on the key fields for this second join. Tis is the line John codes as "the key field(s) to the spending table".

                                                   

                                                   

                                                   

                                                   

                                                    • Re: Help Using Nulls and Joins
                                                      John Witherspoon

                                                      Since the ContractedSpend table is coming from an Excel file instead of the binary load, we can go back to an applymap.  We just need to do it during a left join instead of during the intial load (since the initial load is done by binary load, we can't touch it).

                                                       

                                                      Binary ".\Artemis_Qlikdatabase.qvw";

                                                       

                                                      ContractedSpend:
                                                      MAPPING LOAD
                                                      zzContracted_SupplierID
                                                      ,Contracted?
                                                      FROM [Contracted Spend - received.xls] (biff, embedded labels, table is UploadNew$)
                                                      ;
                                                      LEFT JOIN (Purchases)
                                                      LOAD
                                                      Unique,
                                                      applymap('ContractedSpend',Supplier,'N') as Contracted?
                                                      RESIDENT Purchases
                                                      ;

                                                        • Re: Help Using Nulls and Joins
                                                          Richard Sheppard

                                                          Hi both,

                                                           

                                                          Thanks for your time on this, although experiancing a few issues still.

                                                           

                                                          Binary ".\Artemis_Qlikdatabase.qvw";

                                                           

                                                          ContractedSpend:
                                                          MAPPING LOAD
                                                          zzContracted_SupplierID             // I use the "Unique" field as combines the SupplierID with Material, ie if contracted supplier is linked to a random Material, spend is not deemed "Contracted"
                                                          ,Contracted?
                                                          FROM [Contracted Spend - received.xls] (biff, embedded labels, table is UploadNew$)
                                                          ;
                                                          LEFT JOIN (Purchases)
                                                          LOAD
                                                          Unique,
                                                          applymap('ContractedSpend',Supplier,'N') as Contracted?
                                                          RESIDENT Purchases
                                                          // have used "Unique" instead of Supplier, as this is my key field in my mapping load?

                                                           

                                                          ---------------------------------------

                                                          Making the above two changes, i get an "Out of Virtual and/or logical memory" error message, which could imply I have a synthetic join?

                                                           

                                                          Can anyone confirm whether my above changes to the script are valid/logical, or whether its these changes that have caused the issue?

                                                           

                                                          Kind regards,

                                                           

                                                          Rich

                                                            • Re: Help Using Nulls and Joins
                                                              Patrick Laredo

                                                              hi Rich,

                                                               

                                                              you have miscoded the applymap function.

                                                               

                                                              change your code as follows and see if this improves the situation:

                                                               

                                                              Binary ".\Artemis_Qlikdatabase.qvw";

                                                               

                                                              ContractedSpend:
                                                              MAPPING LOAD
                                                              zzContracted_SupplierID           
                                                              ,Contracted?
                                                              FROM [Contracted Spend - received.xls] (biff, embedded labels, table is UploadNew$)
                                                              ;

                                                               

                                                                   LOAD
                                                                   *,
                                                                   applymap('ContractedSpend',Unique,'N') as Contracted?
                                                                   RESIDENT Purchases
                                                                   ; 

                                                               

                                                              the applymap looks for your "unique" value in the contractedspend table and  takes the value from teh contract? filed if the seaxcrh is sucesful. If not it will put 'N' in the Contracted?

                                                               

                                                               

                                                                • Re: Help Using Nulls and Joins
                                                                  Richard Sheppard

                                                                  error.gifHi Pat,

                                                                   

                                                                  Thanks for this!

                                                                   

                                                                  Have adjusted my script as prompted (although in the initial "MAPPING LOAD", the key field I am returning in my script is "Unique" vs "zzContracted_SupplierID") - let me know if you think I am incorrect in doing this (ie its a combination of SupplierID and Material - and is a field that exist (and works) in my QV Purchases table?

                                                                   

                                                                  When running the script, my QV document fails to complete - ie it appears to be correct (ie no error messages are being returned), but my "Script Execution Progress" window does not automatically close, denoting full script has been run.

                                                                   

                                                                  From what I can see, the applymap routine has been performed as it appears mid-way through the script, so unsure why its not completing.  According to the "SEP" window, it took 13mins to run, although window has been open for over 2hrs.

                                                                   

                                                                  nb by using the "zzContracted_SupplierID" the issue is still present.

                                                                   

                                                                  Im using QV v8.5 - if that provides any additional insight...?

                                                                   

                                                                  As before, any thoughts greatly appreciated,

                                                                   

                                                                  Kind regards,

                                                                   

                                                                  Rich

                                                                    • Help Using Nulls and Joins
                                                                      Patrick Laredo

                                                                      hi,

                                                                       

                                                                      post the new way in which you have coded your map and subsequent load using the applymap fucntion.

                                                                       

                                                                      Also make sure this is the area where you are having an issue by comenting out this part of the script and confirming that your error is coming from elsewhere.

                                                                       

                                                                      Is PMT_Classification the last load in your script?

                                                                       

                                                                      If so qv is hanging as it tries to build the final datacloud probably due to a circular reference, cartesian join or synthetic key caused by fields inadvertently having the same name in several places.

                                                                        • Re: Help Using Nulls and Joins
                                                                          Richard Sheppard

                                                                          Hi Pat,

                                                                           

                                                                          Thanks for your response!

                                                                           

                                                                          1. I can confirm "PMT_Classification" is my last load in my script.
                                                                          2. Commenting out my "applymap sequence", my QV reloads the data with no problems in just over 10mins (with no synthetic joins etc...)
                                                                          3. Reviewing the "Table Viewer" I cannot identify any duplicate fields that are being introduced as part of the applymap routine (ie 1 common field between tables = "Unique")

                                                                           

                                                                          ----------------------------------------------------------------------------------------

                                                                          Current script being used (which fails to finish data load):

                                                                           

                                                                          ContractedSpend:
                                                                          MAPPING LOAD

                                                                          Unique
                                                                          ,Contracted?
                                                                          FROM [Contracted Spend - received.xls] (biff, embedded labels, table is UploadNew$)
                                                                          ;


                                                                          LOAD *,
                                                                          applymap('ContractedSpend',Unique,'N') as ContractedFlag
                                                                          RESIDENT Purchases;

                                                                           

                                                                          --------------------------------------------------------------------------------------------------------

                                                                          by commenting out the final section of my applymap statement, my reload is still successfull

                                                                           

                                                                          /*

                                                                          LOAD *,

                                                                          applymap('ContractedSpend',Unique,'N') as Contracted?

                                                                          RESIDENT Purchases;

                                                                          */

                                                                           

                                                                          --------------------------------------------------------------------------------------------------------

                                                                          one thing i've realised, is that the "Unique" field in the "Purchases" table is being defined in the previous step of the script - could this be causing the issue (ie the field that the applymap is targetting)?

                                                                           

                                                                          zzTempContractedSpend2:
                                                                          noconcatenate  Load
                                                                          *,
                                                                          if (left(zzFamily,1)<>2, zzFamily & '-' & SupplierID , zzFamily & '-'  & zzClass & '-' & SupplierID) as Unique,  // new field to be added "Unique"
                                                                          Resident Purchases;

                                                                          drop table Purchases;
                                                                          rename table zzTempContractedSpend2 to Purchases;

                                                                           

                                                                          //---------------------------------------------

                                                                           

                                                                          ContractedSpend:
                                                                          MAPPING LOAD

                                                                          Unique
                                                                          ,Contracted?
                                                                          FROM [Contracted Spend - received.xls] (biff, embedded labels, table is UploadNew$)
                                                                          ;

                                                                           

                                                                          LOAD *,
                                                                          applymap('ContractedSpend',Unique,'N') as ContractedTemp
                                                                          RESIDENT Purchases;

                                                                           

                                                                          --------------------------------------------------------------------------------------------------------

                                                                           

                                                                          As before, any thoughts greatly appreciated!

                                                                           

                                                                          Kind regards,

                                                                           

                                                                          Rich

                                                                            • Re: Help Using Nulls and Joins
                                                                              Patrick Laredo

                                                                              hi,

                                                                               

                                                                              it's hard to tell from this distance.

                                                                               

                                                                              However you have isolated the issue as coming from the script which includes the applymap statement.

                                                                               

                                                                              This statement is adding a new field - Contracted?, altho' in your last example it seems to change names several times in the code you post - to your Purchases table. Now look at th code you have posted where you show how the Unique field is created in the Purchases table, again this is a new field. You start by dong a noconcatenate load into a different temporary table, drop the orignal Purchases table then rename your temporary table. This works fine.

                                                                               

                                                                              However you do not seem to follow the same sequence when adding the contracted? field. Why not? I am not sure if this is the cause but if you are still stuck why not test that one out?

                                                                               

                                                                              so your code will look as follows:

                                                                               

                                                                               

                                                                              ContractedSpend:
                                                                              MAPPING LOAD

                                                                              Unique
                                                                              ,Contracted?
                                                                              FROM [Contracted Spend - received.xls] (biff, embedded labels, table is UploadNew$)
                                                                              ;

                                                                               

                                                                              zzTempTableApplyMap:

                                                                               

                                                                              noconcatenate LOAD *,
                                                                              applymap('ContractedSpend',Unique,'N') as ContractedTemp
                                                                              RESIDENT Purchases;

                                                                               

                                                                              drop table Purchases:

                                                                               

                                                                              rename table zzTempTableApplyMap to Purchases;