topic Re: Date is null for Incremental Load in QlikView

Date is null for Incremental Load

Karim_Khan — Wed, 25 Nov 2020 16:16:04 GMT

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?

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,

New to QlikView‌celambarasan‌qlikrajan‌surendraj‌

Re: Date is null for Incremental Load

rwunderlich — Fri, 17 Feb 2017 17:08:23 GMT

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

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

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Date is null for Incremental Load

CELAMBARASAN — Mon, 20 Feb 2017 07:08:34 GMT

Hi,

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

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.

Re: Date is null for Incremental Load

Karim_Khan — Mon, 20 Feb 2017 07:56:42 GMT

Hi Sir,

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

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;

Re: Date is null for Incremental Load

CELAMBARASAN — Mon, 20 Feb 2017 08:36:03 GMT

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.

Re: Date is null for Incremental Load

Karim_Khan — Mon, 20 Feb 2017 08:45:03 GMT

I think ur approach is perfect