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

[#1]

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
123	Dallas	$12.00
234	Dallas	$54.00
345	Detroit	$56.00
456	Detroit	$23.00
567	Chicago	$67.00
678	Dallas	$87.00
789	Chicago	$43.00

Updated Values

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

Desired Results

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

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

[#2]

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;

[pgrenier · Accepted solution]

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

[#4]

Thank you, Philippe.  That does the trick.  I thought the mapping function was more limited.  Also, thank you for your quick response!