Update certain records in QVD

[joseph123]

lets say I have QVD  --- ABC.qvd

The structure is as follows

a   b   c

1   2    3

4   5

7   8   9

I have another table XYZ as below:-

a   c   

1   10    

4   6

7   20   

I want to update only that one record of ABC.qvd where c is null (using data from XYZ) and the final ABC.qvd to be as below:-

a   b   c

1   2    3

4   5    6

7   8   9

I have a solution as below:-

- tab1 - load from ABC only records where c is null

- left join (tab1) with XYZ

- tab2 - load from ABC only records where c is not null

- concatenate tab1 and tab2 and store it as ABC.qvd

My question is can this be done in a more efficient / better  manner than what I have described

[MayilVahanan]

Hi @joseph123 

Try like below

ABC:
LOAD * INLINE [
a, b, c
1, 2, 3
4, 5,
7, 8, 9
];

Join
Load a, c as d Inline
[
a,c
1,10
4,6
7,20
];

Final:

Load a,b, if(isnull(c) or len(Trim(c))=0,d,c) as c Resident ABC;

Store Final into ABC.qvd;

DROP Table ABC;

[Vegar · Accepted solution]

If you are pretty effective with your method. You could skip the last step by concatenate straight into tab1. Like this.t

• Tab1 - load from ABC only records where c is null
• left join (Tab1) with XYZ
• Concatenate into Tab1 load from ABC only records where c is not null

[joseph123]

cool I like this!! Thanks @MayilVahanan