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: 
Karim_Khan
Creator III
Creator III

Date is null for Incremental Load

Hi Team,

    I was writinng the code for handling null date becasue in our db there is some date for DATE1 which is null and DATE2 which is not null.

for overriding this issue I was writing the code without if condition.I wanted to know from our qlikTeam will it work properly or is there any other thing which i need to have to done?

A:

LOAD

date(max(DATE1) OR max(DATE2),'YYYY-MM-DD hh:mm:ss') as PEAK_DT FROM

$(vQvdPaths)(qvd);

LET LastExecTime=Peek('PEAK_DT',-1,'A');

DROP TABLE A;

Regards,

KK

New to QlikViewcelambarasanqlikrajansurendraj

KK
1 Solution

Accepted Solutions
CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

You need to create 2 variables to keep these 2 date values.

A:

LOAD

date(max(DATE1) ,'YYYY-MM-DD hh:mm:ss') as PEAK_DT1

,date(max(DATE2) ,'YYYY-MM-DD hh:mm:ss') as PEAK_DT2

FROM

$(vQvdPaths)(qvd);

LET LastExecTimeDate1=Peek('PEAK_DT1',-1,'A');

LET LastExecTimeDate2=Peek('PEAK_DT2',-1,'A');


DROP TABLE A;

And in your Incremental load part these 2 date variables has to be used for comparison against the corresponding date fields.

View solution in original post

5 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

This part is incorrect. It would return a 0 or -1.

date(max(DATE1) OR max(DATE2),'YYYY-MM-DD hh:mm:ss')


What you want is:

date(rangemax(DATE1, DATE2),'YYYY-MM-DD hh:mm:ss')


or

date(alt(DATE1, DATE2),'YYYY-MM-DD hh:mm:ss')


-Rob

http://masterssummit.com

http://qlikviewcookbook.com


CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

You need to create 2 variables to keep these 2 date values.

A:

LOAD

date(max(DATE1) ,'YYYY-MM-DD hh:mm:ss') as PEAK_DT1

,date(max(DATE2) ,'YYYY-MM-DD hh:mm:ss') as PEAK_DT2

FROM

$(vQvdPaths)(qvd);

LET LastExecTimeDate1=Peek('PEAK_DT1',-1,'A');

LET LastExecTimeDate2=Peek('PEAK_DT2',-1,'A');


DROP TABLE A;

And in your Incremental load part these 2 date variables has to be used for comparison against the corresponding date fields.

Karim_Khan
Creator III
Creator III
Author

Hi Sir,

    If I will go with below logic what will be the issue

A:

LOAD

date(ALT(max(DATE1),max(DATE2),'YYYY-MM-DD hh:mm:ss')) as PEAK_DT FROM

$(vQvdPaths)(qvd);

LET LastExecTime=Peek('PEAK_DT',-1,'A');

DROP TABLE A;

KK
CELAMBARASAN
Partner - Champion
Partner - Champion

My assumption was the records with Date1 will have different data set and Date with DATE2 will have a different data set.

So, in that case lets take the below example data

ID, DATE1, DATE2

1, 02/19/2017, NULL

2, NULL, 02/20/2017

Your Last Execution time will be 02/20/2017

In the next day processing

ID, DATE1, DATE2

1, 02/19/2017, NULL

2, NULL, 02/20/2017

3, 02/20/2017, NULL//New record -- This wont be loaded because that logic considers it as already loaded record.

4, NULL, 02/21/2017//New record -- This will be loaded

If this scenario is possible then there will be miss of some records. If that is not possible then go ahead with the solution that you mentioned.

Karim_Khan
Creator III
Creator III
Author

I think ur approach is perfect

KK