Date is null for Incremental Load

[Karim_Khan]

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 QlikView‌celambarasan‌qlikrajan‌surendraj‌

[rwunderlich]

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 · Accepted solution]

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]

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;

[CELAMBARASAN]

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]

I think ur approach is perfect