topic Re: How do I apply a small update to a large QVD? in QlikView

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

Tue, 10 Dec 2013 22:01:43 GMT

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.

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

Tue, 10 Dec 2013 22:06:52 GMT

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?

pgrenier — Tue, 10 Dec 2013 22:49:33 GMT

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

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

Wed, 11 Dec 2013 13:26:04 GMT

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