Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
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

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
Luminary Alumni
Luminary Alumni

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

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

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

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