topic How to join and update qvd table? in QlikView

How to join and update qvd table?

Wed, 25 Nov 2020 16:16:04 GMT

Dear all,

I want to update an existing QVD table with specific observation. See the picture below:

I have created Table 1 and then stored in "Valve.qvd" file.

At a given moment, I want to update Valve.qvd with one observation as displayed in Table 2.

The column Value = 2 has a new modified Description = Valve opened!

Valve closed must be replaced by Valve opened! in the 2nd observation.

The desired result is Table 3 and then Table 3 must be saved under the same file name "Valve.qvd".

My question: do you have a proper solution how to achieve this?

It seems that JOIN do not merge but create extra observation which is not desired.

Thank you by advance,

Best regards,

Cornelis

Re: How to join and update qvd table?

marcus_sommer — Sun, 15 Jan 2017 08:13:38 GMT

Try it with concatenate like this one:

ValveUpdate:

load Value, Description from Table2;

concatenate(ValveUpdate)

load Value, Description from Valve.qvd (qvd) where not exists(Value);

store ValveUpdate into Valve.qvd (qvd);

- Marcus

Re: How to join and update qvd table?

Mon, 16 Jan 2017 05:56:27 GMT

Dear Marcus,

Thank you for your swift reply.

I have tried your idea in the script:

/*Create a table Valve*/
Table1:
LOAD * INLINE
[
value, Description
1, Valve opened
2, Valve closed
3, Vale pending
]
;
/*Save table Valve as qvd file*/
STORE Table1 INTO ;

Drop table Table1;

/* Create new Valve table but want to overwrite
Value = 2 is now "Valve opened"
JOIN, (merge) in Valve qvd*/
Table2:
LOAD * INLINE
[
value, Description
2, Valve opened!
]
;

ValveUpdate:
load
value,
Description
Resident Table2;

concatenate(ValveUpdate)

load
value,
Description
from (qvd) where not exists(Value);

store ValveUpdate into (qvd);

but receive this error:

Could you please check?

Best regards,

Cornelis.

Re: How to join and update qvd table?

khalander — Mon, 16 Jan 2017 06:24:04 GMT

Hi,

Create mapping table with table2 like below

Table2:

Mapping

LOAD * INLINE [

Value, Description

2, Value Opened

];

LOAD Value,

ApplyMap('Table2',Value,Description) as Description;

Table1:

LOAD * INLINE [

Value, Description

1, Value Opened

2, Value CLosed

3, Value Pending

];

hope it will work

Re: How to join and update qvd table?

jonathandienst — Mon, 16 Jan 2017 06:35:47 GMT

ValveUpdate is being auto-concatenated to Table2. You need:

ValveUpdate:

NoConcatenate

load

value,

Description

Resident Table2;

Re: How to join and update qvd table?

Anonymous — Mon, 16 Jan 2017 06:41:49 GMT

Hi there,

I guess Insertion with Updating will help you solve your problem.

please go through the link and find your solution.

Incremental Load in QlikView – Part2 – Learn QlikView

Thanks

Re: How to join and update qvd table?

Tue, 17 Jan 2017 06:13:22 GMT

Dear Jonathan,

Thank you for your support. This is exactly what I'm looking for.

The field in script where not exists(value) is important to make a distinction between numbers 1,2 and 3

Best regards,

Cornelis

Re: How to join and update qvd table?

Tue, 17 Jan 2017 06:14:46 GMT

Hi Dada,

That looks interesting, you provide an alternative.

This is a good learning point.

Best regards,

Cornelis

Re: How to join and update qvd table?

Tue, 17 Jan 2017 06:17:38 GMT

Yes, this is a good material.

The point is to overwrite a record based on one key-field as indicator: value = 2.

Jonathan (see above) provides me the solution together with Marcus.

Re: How to join and update qvd table?

Tue, 17 Jan 2017 06:18:45 GMT

Thanks Marcus.

Jonathan has just given extra information to complete the script without error.

Best regards,

Cornelis.