Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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;
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
Thank you, Philippe. That does the trick. I thought the mapping function was more limited. Also, thank you for your quick response!