3 Replies Latest reply: Dec 11, 2013 8:26 AM by Brian Koehn RSS

    How do I apply a small update to a large QVD?

      Let's say I have a massive QVD that get created once/month.

       

      How do I update some of the values in one of the columns?

       

      For example:  Original Master Data

      O_Mstr.VIN O_Mstr.Loc O_Mstr.Damage
      123Dallas$12.00
      234Dallas$54.00
      345Detroit$56.00
      456Detroit$23.00
      567Chicago$67.00
      678Dallas$87.00
      789Chicago$43.00

       

      Updated Values

      Upd.VIN Upd.Damage
      123$114.00
      234$216.00
      678$42.00

       

      Desired Results

      VIN Loc Damage
      123Dallas$114.00
      234Dallas$216.00
      345Detroit$56.00
      456Detroit$23.00
      567Chicago$67.00
      678Dallas$42.00
      789Chicago$43.00

       

      Attached is one solution, but it seems like there should be something more elegant.

        • Re: How do I apply a small update to a large QVD?

          I guess I could have posted the script outside the QVD:

           

          NewMaster:

          LOAD VIN,

               Damage

          FROM

          UpdatedData.qvd (qvd);  // Let's say I have 100 records I want to change.

           

          LOAD VIN,

               Damage

          FROM

          Master.qvd (qvd)

          Where Not Exists(VIN);  //  Let's say I have 999,990 records I don't need to change.

           

          Join(NewMaster)

          LOAD VIN,

               Loc

          FROM

          Master.qvd (qvd);        //  Is there a more economical way to do this?

           

           

          It just seems like you should be able to do something like:

          Update Master

             set Master.Damage = UpdatedData.Damage

          From Master  INNER JOIN  UpdatedData on Master.VIN = UpdatedData.VIN;

          • Re: How do I apply a small update to a large QVD?
            Philippe Grenier

            Hello Brian,

             

            Presuming the VIN values are unique, you could save a few steps using the ApplyMap() function along with a previously loaded mapping table on your new Damage values as follows, replacing the code under the "Cumbersome" tab in your original document:

             

            map_UpdatedData:

            Mapping LOAD VIN, Damage

            From UpdatedData.qvd (qvd);

             

            Master:

            LOAD VIN, Loc, ApplyMap('map_UpdatedData', VIN, Damage) as Damage

            From Master.qvd (qvd);

             

            Regards,

             

            Philippe