5 Replies Latest reply: Jul 20, 2010 3:31 AM by At titude RSS

    Incremental load with multiple dates

    dbekas

      I am currently doing an incremental load of historical information by loadig a QVD with the prior data, appending the current day's data, then rewriting the QVD. It looks something like this:

      • Read the date from the first record of the new file and store it in a variable:
        FindDate:
        LOAD InvDate AS BillingInfoFileDate
        FROM BillingInfo.txt (ansi, txt, delimiter is ';', embedded labels, msq)
        WHERE RecNo()=1;
        LET vBillingInfoFileDate= num(peek('BillingInfoFileDate', 0));

      • Load the existing history from the QVD, excluding the date in the new file (I do this to allow for repeated loads throughout the day. It also allows me to correct a previous load of incorrect data):
        BillingHistory:
        LOAD *
        FROM BillingHistory.qvd (qvd)
        WHERE InvDate <> $(vBillingInfoFileDate);

      • Add the records from the new file and rewrite the QVD:
        CONCATENATE (BillingHistory)
        LOAD *
        FROM BillingInfo.txt (ansi, txt, delimiter is ';', embedded labels);
        STORE * FROM BillingHistory INTO BillingHistory.qvd;

      This method works great right now because I know that the new BillingInfo file will always contain only one date's worth of information. That's about to change because I have to start picking up activity over the weekend and the job that runs Monday morning will cover up to three days. I need a way to read the new file, get all dates included in that file and exclude those dates from the QVD load to prevent loading duplicate records. Individual daily loads on Sat and Sun are not possible.

      Thanks in advance for any ideas on how I might approach this.
      Dino

        • Incremental load with multiple dates
          Rob Wunderlich

          Does every row of the txt file have a InvDate? If so, you can approach it this way.

          1. LOAD all the data from BillingInfo.txt.

          2. LOAD * BillingHistory.qvd (qvd) WHERE NOT exists(InvDate)

          -Rob

            • Incremental load with multiple dates
              dbekas

              Yes, every row has among other things, Date and Invoice Number. When I try the method above, I end up losing all invoices from the previous dates except one for each date. I mocked up up some data to test and here's what happened:

              First Load File:
              Date,InvNbr
              1/1/2009,110
              1/1/2009,111
              1/1/2009,112
              1/1/2009,113
              1/1/2009,114
              1/1/2009,115
              1/1/2009,116
              1/1/2009,117
              1/1/2009,118
              1/1/2009,119

              Loads and writes to QVD as expected (first time around I didn't load the QVD because it didn't exist yet.

              The I load the next file:
              Date,InvNbr
              1/2/2009,120
              1/2/2009,121
              1/2/2009,122
              1/2/2009,123
              1/2/2009,124
              1/2/2009,125
              1/2/2009,126
              1/2/2009,127
              1/2/2009,128
              1/2/2009,129

              What seems to happen is that the initial file loads as expected. I load the QVD, which doesn't contain1/2/2009 yet, but only the first entry from 1/1/2009 loads and I end up with this:

              1/1/2009110
              1/2/2009120
              1/2/2009121
              1/2/2009122
              1/2/2009123
              1/2/2009124
              1/2/2009125
              1/2/2009126
              1/2/2009127
              1/2/2009128
              1/2/2009129


              Then I load a file for 1/3/2009 and end up with this:

              1/1/2009110
              1/2/2009120
              1/3/2009130
              1/3/2009131
              1/3/2009132
              1/3/2009133
              1/3/2009134
              1/3/2009135
              1/3/2009136
              1/3/2009137
              1/3/2009138
              1/3/2009139


              So it seems that the exists() is loading the first invoice of each date from the qvd, then rejecting subsequent records with the same date but different invoice numbers.

                • Incremental load with multiple dates
                  Rob Wunderlich

                  Sorry for my incomplete answer. What you need to do is define a combination of fields that represent a Key and use that in the exists.

                  Date & '|' & InvNbr as PK

                  and then

                  WHERE NOT exists(PK)

                  If you have large keys, you can reduce the size by using authonumberhash128() -- but then you have to repeat the hash in the QVD concatenate. For a complete Incremental Reload example, including the autonumberhash128 technique, see the QV Cookbook sample "Incremental reload template and example".

                  You can get the QV Cookbook from:

                  http://www.robwunderlich.com/Download.html

                  -Rob

                • Incremental load with multiple dates

                  Hi Rob,

                  Can you please do the required changes in my document so that incremental load(insert,update and delete) works.

                  Please look into the application that I have attached and do the required changes.

                  http://community.qlik.com/forums/t/28159.aspx