topic Re: Changing a variable in one table to an updated status in another table in QlikView

Changing a variable in one table to an updated status in another table

zach_paz — Thu, 07 Nov 2013 19:33:53 GMT

Hello,

Is there a way to update a variable/column in an existing QVD with a table that contains updates for that table.

I have one QVD:

Table1:

ID Status

1 Converted

2 Inquiry

3 Inquiry

4 Converted

5 Inquiry

And one update file:

Table2:

ID Status

2 Converted

5 Converted

I want Table1 to be updated to look like this:

Table1:

ID Status

1 Converted

2 Converted

3 Inquiry

4 Converted

5 Converted

Where Table2 updates the records in Table1, but keeps the records in Table1 that do not have an update.

Thanks in advance!

Re: Changing a variable in one table to an updated status in another table

nicolett_yuri — Thu, 07 Nov 2013 19:39:03 GMT

Try it

Re: Changing a variable in one table to an updated status in another table

zach_paz — Thu, 07 Nov 2013 20:18:37 GMT

Thanks Yuri! I think it worked, but I need to include all of the other fields in the table (alpha_tmp) below. The script below only gives me the lead_id and status_name. Do I need to change what I loaded in that last part?

alpha_tmp:

LOAD lead_id,

status_name,

city,

state,

address,

cost

FROM

(qvd);

LEFT JOIN

LOAD LeadID as lead_id,

status_name as status_update,

status_date as status_date_update

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

alpha:

NoConcatenate

Load

lead_id,

if( NOT IsNull(status_update), status_update, status_name) as status_name

RESIDENT alpha_tmp;

DROP TABLE alpha_tmp;

Thanks again!

Re: Changing a variable in one table to an updated status in another table

nicolett_yuri — Thu, 07 Nov 2013 20:40:42 GMT

The idea is the same.

If the current field return from another table, use the if NOT IsNull same example.

if you need more help, send me an example of qvw

Re: Changing a variable in one table to an updated status in another table

zach_paz — Thu, 07 Nov 2013 20:44:53 GMT

Got it working!

Just needed to add the fields that I wanted included in the output in to the bottom.

...

alpha:

NoConcatenate

Load lead_id,

city,

state,

address,

datapartner,

cost,

if( NOT IsNull(status_update), status_update, status_name) as status_name

RESIDENT alpha_tmp;

DROP TABLE alpha_tmp;

Thanks for your help Yuri!