Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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.

Tags (4)
1 Solution

Accepted Solutions
pgrenier
Contributor III

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

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

3 Replies
Not applicable

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;

pgrenier
Contributor III

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

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

Not applicable

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

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

Community Browser