Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lucasdavis500
Creator III
Creator III

Incremental Load not working

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?

1 Solution

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

View solution in original post

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

lucasdavis500
Creator III
Creator III
Author

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.

lucasdavis500
Creator III
Creator III
Author

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.

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)

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.

lucasdavis500
Creator III
Creator III
Author

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?

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';) .