Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

yelin_nyu
Not applicable

modify qvd field

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)
OrderAccount
11
21
32
42
account master (current)

AaccountStatusnew acct
1Inactive4
2active2
3inactive4
4active4
Main table after update
order, acct
1          4
2          4
3          2
4          2
7 Replies
Oleg_Troyansky
Not applicable

Re: modify qvd field

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
Not applicable

Re: modify qvd field

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

Not applicable

Re: modify qvd field

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
Not applicable

Re: modify qvd field

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)

Not applicable

Re: modify qvd field

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
Not applicable

Re: modify qvd field

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.

Not applicable

Re: modify qvd field

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