7 Replies Latest reply: Nov 26, 2014 9:32 AM by O'Brian Newell RSS

    Joining and Returning Values

    O'Brian Newell

      Hi,

           I have a problem, I am trying to join multiple tables together and lookup up the rate. Once this is possible I will then do a conversion. So far this is my script below, attempting with a straight LOOKUP. I have also tried with APPLYMAP with no avail.

      Any help is much appreciated.

       

      [TABLE1]:

      LOAD ID,

           LABL_NAME,

           RISK,

           HS

      FROM DRIVE;

       

      JOIN LOAD ID, 

           LABL_NAME,

           RISK,

           HS

      FROM DRIVE;

       

      JOIN LOAD ID, 

           LABL_NAME,

           RISK,

           HS

      FROM DRIVE;

       

      [RATES]:

      LOAD DATE,

           RATE,

           CCY,

           CCYDATE

       

      LOAD*;

           LOOKUP('RATE','CCYDATE',CCYDATE2,'RATES') AS RATE;

       

      LOAD*,

           (CCY&DATE2) AS CCYDATE2;

      LOAD*,

           NUM(DATE) AS DATE2;

       

      [TABLE2]:

      LOAD DATE,

           ID,

           CCY,

           CODE

      FROM DRIVE;

       

      JOIN LOAD DATE,

           ID,

           CCY,

           CODE

      FROM DRIVE;

        • Re: Joining and Returning Values
          Jason Michaelides
          1. I wouldn't recommend ever using Lookup() on anything other than a very small table as it is pretty slow.
          2. in Table 1 and Table 2, why are you JOINing rather than using CONCATENATE? They are all the same fields and I'm guessing different subsets of data?
          3. I can't figure out where DATE is coming from...
          4. I can't see how the RATES data links to the other tables?

           

          Basically, the order of things should usually be:

           

          1. Load Mapping Table - MAPPING LOAD
          2. Load Data and use ApplyMap() during the load
          3. Load other Data

          Hope this helps,

           

          Jason

            • Re: Joining and Returning Values
              O'Brian Newell

              Hello Jason,

               

                   Could you give an example?

                • Re: Joining and Returning Values
                  Jason Michaelides

                  My best guess for what you're trying to do...

                   

                  [TABLE1]:

                  LOAD ID,

                       LABL_NAME,

                       RISK,

                       HS

                  FROM DRIVE;

                   

                  CONCATENATE [TABLE1]   //this isn't strictly necessary as QV will auto-concatenate identical tables

                   

                  LOAD ID,

                       LABL_NAME,

                       RISK,

                       HS

                  FROM DRIVE;

                   

                  CONCATENATE [TABLE1]     //this isn't strictly necessary as QV will auto-concatenate identical tables

                   

                  JOIN LOAD ID,

                       LABL_NAME,

                       RISK,

                       HS

                  FROM DRIVE;

                   

                  Map_Rates:

                  MAPPING LOAD

                       DATE&CCY

                       ,RATE

                  FROM....;

                   

                  [TABLE 2]:

                  LOAD

                       ID,

                       DATE,

                       ApplyMap('Map_Rates',DATE&CCY,0)     AS     RATE,

                       CCY,

                       CODE

                  FROM...;

                   

                  CONCATENATE [TABLE2]

                   

                  LOAD

                       ID,

                       DATE,

                       ApplyMap('Map_Rates',DATE&CCY,0)     AS     RATE,

                       CCY,

                       CODE

                  FROM...;