modify qvd field

[yelin_nyu]

How to update a field in your qvd without touching other parts of qvd?

I have a main table where i regularly save it as weekly qvd, and a master table for account. Account is a foreign key in main table. occassionally, account number changes in master table. I need to manually update the account field in my main table. How do i do that?

I read about you can update qvd with incremental load, never used it myself and not very familiar with it either.

example:

main (history)

Order	Account
1	1
2	1
3	2
4	2

account master (current)

Aaccount	Status	new acct
1	Inactive	4
2	active	2
3	inactive	4
4	active	4

Main table after update

order, acct

1          4

2          4

3          2

4          2

[Oleg_Troyansky]

Every time you store a QVD, the entire file is being written on disk. So, from this perspective, you can't only update one column wihtout touching other parts.

You can carefully craft your code in such a way that other columns are not changing any values, but technically they will be re-written one way or another...

[yelin_nyu]

when i mentioned not change the other parts, i meant don't change the values. for example if my original main table had another column called value1

order acct value1

1          1     x

2          1     y

3          2     z

4          2     a

the table should look like this after updating it

order acct value1

1          4     x

2          4     y

3          2     z

4          2     a

[#4]

You can use Mapping Table.

AccountMap:

Mapping Load

     OldAccount,

     NewAccount

     From AccountMaster:

History:

Load

     Order,

     ApplyMap('AccountMap', acct) as NewAcct,

     ...

     from History;

Or

AccountMap:

Mapping Load

     OldAccount,

     NewAccount

     From AccountMaster:

Map acct Using AccountMap;

History:

Load

     Order,

     acct,

     ...

     from History;

Hope this helped.

Sean

[yelin_nyu]

thanks sean, your method is very nice. This is my fault, I forgot to mention the column of new account does not represent the updated list of new accounts.

so the actual account master is more like this:

account, status, new acct

1,           inactive, 4

2,          active,     99999200403 (some random stuff I don't care, and i do NOT want to use)

3,          inactive,  4

4,          active,     093284259 (do not want to use this value at all)

[#6]

I don't know if I am understanding your question correctly. 

If account Status is Inactive, you want to replace it to 'new acct' values and if it is active, just leave it as is?

In order words, you want to change only account# 1 and 3 to 4 and 2 and 4 as is, am I right?

Sean

[yelin_nyu]

Yes, and i want to overwrite the qvd with new acct info. so when i load it in next time, i will only see 4, not 1 or 3. Also accounts can change again and again, say, account 4 and 2 becomes inactive, and the new account is 5, it will change all 4 and 2 accounts on main tbl to 5.

Think of this as managing clients' order system. clients' account opening, closing, getting combined into 1 account, etc. So the account num on order table need to change, otherwise i can't link account details on acct master.

[#8]

Please take a look at the example I have attached. 

You will be creating a Mapping table (AccountMap) for only Inactive accounts. 

Then when you are loading history table, you will replace inactive accounts to whatever you assigned in the Customer master file. 

Then you can store it into qvd. 

Sean