5 Replies Latest reply: Dec 5, 2011 10:54 AM by Anne Duffy RSS

    Lookup function for script

      Hi guys

       

      I have a crosstable load , where it has staff names , work day and work status

       

      Looks a bit like this

       

      CROSSTABLE

      Working dayStaff Work Status
      01/01/2011AnnHalf Day Holiday
      01/01/2011BarryWorking
      01/01/2011NualaWorking
      01/01/2011FredWorking
      01/01/2011JohnSick
      02/01/2011AnnWorking
      02/01/2011BarryWorking
      02/01/2011NualaWorking
      02/01/2011FredWorking
      02/01/2011JohnSick
      03/01/2011AnnWorking
      03/01/2011BarryWorking
      03/01/2011NualaWorking
      03/01/2011FredWorking
      03/01/2011John

       

      Working

       

       

      But I have another load - which is a table of when staff have left - so Barry left on 1/1/11

       

      LEAVETABLE

      Staff End Date
      Ann
      Barry01/01/2011
      Nuala
      Fred
      John
      Ann
      Barry
      Nuala
      Fred
      John
      Ann
      Barry
      Nuala
      Fred
      John

       

      I would like to be able to

       

      On the CROSSTABLE look up the staff  on the

      LEAVETABLE and if the End date is the same or less than Workdate then change workstatus to Left :

       

      Working dayStaff Work Status
      01/01/2011AnnHalf Day Holiday
      01/01/2011BarryLEFT
      01/01/2011NualaWorking
      01/01/2011FredWorking
      01/01/2011JohnSick
      02/01/2011AnnWorking
      02/01/2011BarryLEFT
      02/01/2011NualaWorking
      02/01/2011FredWorking
      02/01/2011JohnSick
      03/01/2011AnnWorking
      03/01/2011BarryLEFT
      03/01/2011NualaWorking
      03/01/2011FredWorking
      03/01/2011JohnWorking

       

      Can anyone help with this please ?

        • Re: Lookup function for script
          Miguel Angel Baeyens de Arce

          Hi,

           

          Use a mapping table with the Staff and the End Date, and use it after the crosstable load, something like

           

          LeftMap:
          MAPPING LOAD Staff,
               "End Date"
          FROM LeftStaff.qvd (qvd);
          
          FinalTable:
          LOAD "Working Day",
               Staff,
               If("Working Day" <= ApplyMap('LefMap', Staff), "Work Status", 'Left') AS "Work Status"
          RESIDENT Crosstable;
          
          DROP TABLE Crosstable;
          

           

          Note the single and double quotes.


          Hope that helps.

           

          Miguel

            • Lookup function for script

              Hi Miguel

               

              I tried the above, see script pasted in but QV does not seem to have Workstatus at all now- Can you advise please ? I think its because its trying to map on a Crosstable load< No errors are showing on the load :

               

               

               

              LOAD Name as Staff,
                   [Staff #],
                   Start,
                   End as [End Date],
                   Unit,
                   Status,
                   [C/Fwd],
                   Spec,
                   Allow,
                   BH,
                   Total
                  
              FROM
              [S:\Copies\Holiday 2011 Jan to Decv3.xls]
              (biff, embedded labels, header is 1 lines, table is [Ex staff$]);

               

              Crosstable:
              Crosstable (Staff,WorkStatus)
              LOAD
              Date as [Working Day],
                   [Murphy Anne],
                   [Boyle Barry],
                   [Dwyer Tom],
                   [Hope Fred],
                   [Martin Larry]
                  
              FROM
              [S:\Copies\Holiday 2011 Jan to Decv3.xls]
              (biff, embedded labels, table is Holidays$)
              Where Date>'06/03/2011';

              LeftMap:
              MAPPING LOAD  Name,
                           "End"
              FROM [S:\Copies\Holiday 2011 Jan to Decv3.xls]
              (biff, embedded labels, header is 1 lines, table is [Ex staff$]);

              FinalTable:
              LOAD "Working Day",
                   Staff,
                   If("Working Day" <= ApplyMap('LeftMap', Staff), "WorkStatus", 'Gone') AS "WorkStatus"
              RESIDENT Crosstable;

              DROP TABLE Crosstable;

              LOAD Team,
                   Managers
              FROM
              [S:\Copies\Holiday 2011 Jan to Decv3.xls]
              (biff, embedded labels, table is Managers$);


              LOAD Staff,
                   Team
              FROM
              [S:\Copies\Holiday 2011 Jan to Decv3.xls]
              (biff, embedded labels, table is Map$);

              LOAD WorkStatus,
                   Here
              FROM
              [S:\Copies\Holiday 2011 Jan to Decv3.xls]
              (biff, embedded labels, table is [Work Map$]);

                • Lookup function for script

                  sorry I meant to add the map ... Workstatus is her but as part of another load "Work Map"

                   

                  map.bmp

                  • Re: Lookup function for script
                    Miguel Angel Baeyens de Arce

                    Hi,

                     

                    At a first glance, your code seems to be OK. Once the Crosstable load has finished, the table has the same properties as any other table, so you can use it as a resident table just fine. The mapping table from the excel file should work right as well.

                     

                    But what may be happening is that the "FinalTable" is being concatenated to the "Crosstable", because they have the same number of fields and all fields are named the same. Note that automatic concatenation works in any table in the script, regardless where the original table is, and not only with the previous table. After that, you are DROPping "Crosstable" that now has all records from original crosstable load and applymap table (they are only one table).

                     

                    To avoid that happening, just put NOCONCATENATE before the LOAD in that table, and that would do:

                     

                    FinalTable:
                    NOCONCATENATE LOAD "Working Day",
                         Staff,
                         If("Working Day" <= ApplyMap('LeftMap', Staff), "WorkStatus", 'Gone') AS "WorkStatus"
                    RESIDENT Crosstable;
                    

                     

                    Hope that helps.

                     

                    Miguel