How to join and update qvd table?

[#1]

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

[marcus_sommer]

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

[#3]

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.

[khalander]

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

[jonathandienst · Accepted solution]

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

ValveUpdate:

NoConcatenate

load

value,

Description

Resident Table2;

[Anonymous]

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

[#7]

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

[#8]

Hi Dada,

That looks interesting, you provide an alternative.

This is a good learning point.

Best regards,

Cornelis

[#9]

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.

[#10]

Thanks Marcus.

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

Best regards,

Cornelis.