topic Re: modify qvd field in QlikView

modify qvd field

yelin_nyu — Fri, 01 Feb 2013 20:18:54 GMT

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

Re: modify qvd field

Oleg_Troyansky — Fri, 01 Feb 2013 20:27:42 GMT

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...

Re: modify qvd field

yelin_nyu — Fri, 01 Feb 2013 20:34:57 GMT

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

Re: modify qvd field

Fri, 01 Feb 2013 21:25:49 GMT

You can use Mapping Table.

AccountMap:

Mapping Load

OldAccount,

NewAccount

From AccountMaster:

History:

Load

Order,

ApplyMap('AccountMap', acct) as NewAcct,

...

from History;

AccountMap:

Mapping Load

OldAccount,

NewAccount

From AccountMaster:

Map acct Using AccountMap;

History:

Load

Order,

acct,

...

from History;

Hope this helped.

Sean

Re: modify qvd field

yelin_nyu — Fri, 01 Feb 2013 21:35:30 GMT

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)

Re: modify qvd field

Fri, 01 Feb 2013 21:47:40 GMT

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

Re: modify qvd field

yelin_nyu — Fri, 01 Feb 2013 21:56:47 GMT

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.

Re: modify qvd field

Sat, 02 Feb 2013 00:25:46 GMT

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