2 Replies Latest reply: Sep 18, 2015 5:33 PM by Gethyn Owen RSS

    Joining data with conditions.

    Gethyn Owen

      Hello,

       

      I need some help with a script which loads data from a .qvd file. I then need to bring in data from and Excel spreadsheet and join it to the data in the .qvd file. There are two possible ways to join the data and if a particular line in the Excel file doesn't join by the first I would like it to join by the second.

       

      I have tried left joining the excel file as shown in the script attached. But I only seem to get the data for the first Excel load and not the second. If I comment out the first, the second loads as it's supposed to.

       

      Any help would be great.

       

      Thanks,

       

      Gethyn.

        • Re: Joining data with conditions.
          Gysbert Wassenaar

          Nest those two applymap mappings:

           

          date([Dispensed Date])&'|'&PurgeChar([Note Detail], 'IDid: ')&'|'&ApplyMap('LloydsMap', Rtrim(Product), ApplyMap('LloydsMapSM', rtrim(Product)) ) as lloydsJoin,

           

          That way you can do the join with one load from the excel file.

            • Re: Joining data with conditions.
              Gethyn Owen

              Hi Gysbert,

               

              Thanks for you reply. What do I need to do in the section that loads the .qvd file that the Excel file joins to? I have the following two lines which represent the two ways that the spread sheet can join. The [Med ID] field will always exist in the .qvd but won't always have a match in the Excel (in the Excel file it could be populated but not be a match) in that case I want to join using the SIMPLE_GENERIC_C.

               

              Date(ACTION_INSTANT)&'|'&MRN&'|'&[Med ID] as lloydsJoin,

              Date(ACTION_INSTANT)&'|'&MRN&'|'&SIMPLE_GENERIC_C as lloydsJoinSM,

               

              Thanks,

               

              Gethyn.