8 Replies Latest reply: Oct 10, 2016 11:33 AM by Nicole Smith RSS

    Incremental Load not working

    Duke Luke

      Hello all, I am trying to do an incremental load and this is my first time using QV in general. I've done Developer/Designer training. More accustomed to SAS so troubleshooting with logs, etc is easier. I have the following code:

       

      //CET Data

      IF NOT ISNULL(QVDCreateTime('$(vQVDPath)CETData.qvd')) THEN

      LoadTime:

      LOAD MAX(DateCreated) AS DateCreated FROM $(vQVDPath)CETData.qvd (QVD) ;

      LET vLastExecTime=DATE(PEEK('DateCreated',0,'LoadTime')) ;

      DROP TABLE LoadTime ;
      END IF ;


      CET:
      LOAD
      Pkey,
      State,
      AccountType,
      DisputeType,
      TaxID,
      Mode,
      AssignedTo,
      ResolvedBy,
      DateResolved,
      DATE(DateCreated) as DateCreated,
      ProductLine,
      CreditDisputeType

      FROM

      [$(vPathname)CET_$(vDataDate).xlsx]
      (
      ooxml, embedded labels, table is Sheet1)

      WHERE DateCreated >= $(vLastExecTime) AND DateCreated < $(vExecTime);

      IF NOT ISNULL(QVDCREATETIME('$(vQVDPath)CETData.qvd')) THEN

      CET_Final:
      CONCATENATE(CET)
      LOAD
      Pkey,
      State,
      AccountType,
      DisputeType,
      TaxID,
      Mode,
      AssignedTo,
      ResolvedBy,
      DateResolved,
      DATE(DateCreated) as DateCreated,
      ProductLine,
      CreditDisputeType

      FROM $(vQVDPath)CETData.qvd (QVD)
      WHERE NOT(EXISTS (Pkey)) ;
      END IF

      IF NoOfRows(CET) > 0 THEN

      STORE CET_FINAL INTO $(vQVDPath)CETData.qvd (QVD) ;

      //DROP TABLE CET ;
      END IF ;

      Here's the issue. For some reason I can't access the CET_Final table. If I uncomment DROP TABLE CET then none of this data is available in my qlikview application. I want to do incremental loads on CET_FINAL with INSERT/UPDATE functionality. I can't seem to get this code to work. In fact, when I use the debugger or simply reload, the log shows two CET tables, the first one has 0 lines fetched, the second actually does have lines fetched.

       

       

      LoadTime << CETData 1 lines fetched

      CET << Sheet1 0 lines fetched
      CET << CETData 1,023,757 lines fetched

       

      I think I have a syntax error somewhere, or that QV is processing this code in a manner I'm not accustomed to, I know loads run bottom to topand the rest of the syntax is (I believe) runs top to bottom....is this the issue?

        • Re: Incremental Load not working
          Nicole Smith

          You are concatenating (i.e. unioning) everything that should be in CET_Final into CET.  So CET_Final never exists because everything is in CET.  If you didn't want to concatenate, you could change to this:

           

          CET_Final:
          NOCONCATENATE LOAD

          Pkey,

          ...


          I can see how looking through the logs can be confusing to determine what is happening, but if you look at the logs in combination with the Table Viewer, it should help.

            • Re: Incremental Load not working
              Duke Luke

              I do want it concatenated. I want everything from CET into CET_FINAL. CET_FINAL should be my QVD that stores my Historical data. CET should be the stepping stone to get the previous months data into CET_FINAL. CET will have two passes when I'm done, First pass takes data from last month, Second pass takes updates from the 2 priors months to that in order to fill in any missing data. For example, This months load would hypothetically have SEPTEMBER data as well as JULY/AUGUST. September data would be NEW data ( with missing values because not everything is "updated") and JULY/AUGUST data pass through would fill in the gaps of what was missing from the data loads at the time they were loaded e.g. july loaded in August, August loaded in September. SEPTEMBERS updated data would happen in November, when I'm loading new October data.

                • Re: Incremental Load not working
                  Duke Luke

                  The thing is, I'm NOT unioning everything in CET FINAL into CET. If I were, I would have September observations. When I reload, there are no September observations. just Jan-August, which was my *initial* data load into a QVD.

                    • Re: Incremental Load not working
                      Nicole Smith

                      First, you are unioning everything from CET_Final into CET because of the order you have it written:

                       

                      IF NOT ISNULL(QVDCreateTime('$(vQVDPath)CETData.qvd')) THEN

                      CET:
                      LOAD
                      Pkey,
                      ...

                      IF NOT ISNULL(QVDCREATETIME('$(vQVDPath)CETData.qvd')) THEN

                      CET_Final:
                      CONCATENATE(CET)
                      LOAD
                      Pkey,
                      ...

                       

                      The CONCATENATE(CET) is telling it to put everything from the load beneath it into the CET table.  So it's unioning CET_Final to CET, resulting in only CET.

                       

                      As far as the September data goes, without seeing your data, I don't know exactly why you aren't getting anything back, but the first CET load isn't bringing anything according to the log that you posted:

                       

                      LoadTime << CETData 1 lines fetched

                      CET << Sheet1 0 lines fetched  <-------------------- No lines fetched for first CET load
                      CET << CETData 1,023,757 lines fetched  <--------------- Lines fetched for CET_Final CONCATENATE(CET) load (it's adding the lines from CET_Final to CET table as I've stated previously)

                        • Re: Incremental Load not working
                          Nicole Smith

                          I think it may have something to do with the way you're using your date variables in the where clause.  You may need to wrap them in single quotes so that they get treated as a string:

                           

                          LET vLastExecTime=chr(39) & DATE(PEEK('DateCreated',0,'LoadTime')) & chr(39) ;


                          You can know for sure by looking at the log file and seeing what's going on with that where clause.

                            • Re: Incremental Load not working
                              Duke Luke

                              I got my incremental load to work ( at least with new observations, haven't tested updated obs yet), but why does QV need to treat these dates as strings in order to evaluate? is this true anytime you are using QV dates, that in order to evaluate dates with eachother you must wrap them in a string?

                                • Re: Incremental Load not working
                                  Nicole Smith

                                  It's not any different than SQL treating dates like strings.  In SQL, if you want to use a date in a where clause, you have to wrap it in single quotes:

                                   

                                  SELECT *

                                  FROM Table

                                  WHERE Date >= '2016-10-01'

                                   

                                  In QV, you also need it in single quotes, and you need to make sure that you're formatting the date (field and variable) in the where clause the same as your date format variable (SET DateFormat='M/D/YYYY';) .