7 Replies Latest reply: Feb 1, 2013 7:25 PM by Sean (Hyunku) Cho RSS

    modify qvd field

    lin ye
      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
        • Re: modify qvd field
          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...

            • Re: modify qvd field
              lin ye

              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
                  Sean (Hyunku) Cho

                  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

                    • Re: modify qvd field
                      lin ye

                      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
                          Sean (Hyunku) Cho

                          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
                              lin ye

                              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
                                  Sean (Hyunku) Cho

                                  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