topic Re: Incremental load issue in QlikView

Incremental load issue

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

Hi All,

I have created incremental load in my project. Here I'm facing issue in fetching the incremental data from DB.

EMP:

LOAD EName, Empid, CreatedDate

FROM [..\QVDs\Employee.qvd] (qvd);

Date:

LOAD max(Timestamp(CreatedDate)) as maxdate

Resident EMP;

let date_modified = Peek('maxdate',0,'Date');

DROP Table EMP;

Incremental_EMP:

SQL SELECT

EName, Empid, CreatedDate

FROM "QV_DB".db.Employee

where CreatedDate >'$(date_modified)';

Concatenate

LOAD EName, Empid, CreatedDate

FROM [..\QVDs\Employee.qvd] (qvd)

Where not Exists(Empid);

Inner Join

SQL SELECT Empid

FROM "QV_DB".db.Employee;

STORE Incremental_EMP into [..\QVDs\Employee.qvd] (qvd);

The issue is in where condition. when we are fetching data from db by using below condition

where CreatedDate >'$(date_modified)';

here variable date_modified will store max date. when we load the data from db it is loading data from max date onwards. but as per our condition it has to fetch data from greater than max date. Please help me to resolve this.

Thanks,

Chandra

Re: Incremental load issue

arvind_patil — Thu, 12 Oct 2017 07:14:31 GMT

Hi Chandra,

Please check date format you have given and which comes from sql:

EMP:

LOAD EName, Empid, CreatedDate

FROM [..\QVDs\Employee.qvd] (qvd);

Date:

LOAD max(Timestamp(CreatedDate)) as maxdate

Resident EMP;

let date_modified = Date(Peek('maxdate',0,'Date'),'DD-MMM-YYYY');

DROP Table EMP;

Incremental_EMP:

SQL SELECT

EName, Empid, CreatedDate

FROM "QV_DB".db.Employee

where CreatedDate >'$(date_modified)';

Concatenate

LOAD EName, Empid, CreatedDate

FROM [..\QVDs\Employee.qvd] (qvd)

Where not Exists(Empid);

Inner Join

SQL SELECT Empid

FROM "QV_DB".db.Employee;

STORE Incremental_EMP into [..\QVDs\Employee.qvd] (qvd);

Thanks,

Arvind Patil

Re: Incremental load issue

Kushal_Chawda — Thu, 12 Oct 2017 07:15:02 GMT

which is the underlying database? Oracle or Sql server?

Re: Incremental load issue

avinashelite — Thu, 12 Oct 2017 07:17:19 GMT

Are you getting any errors?? or your not able to get the data ...could explain bit more on this

Re: Incremental load issue

Anonymous — Thu, 12 Oct 2017 07:20:14 GMT

Sql server

Re: Incremental load issue

kaushiknsolanki — Thu, 12 Oct 2017 07:20:33 GMT

Hi,

WHat do you mean by this statement.

" when we load the data from db it is loading data from max date onwards. but as per our condition it has to fetch data from greater than max date".

It seems similar to me.

Do you mean that it is fetching data for that day also?

If yes then check for the timestamp in the variable.

Regards,

Kaushik Solanki

Re: Incremental load issue

Anonymous — Thu, 12 Oct 2017 07:21:20 GMT

I'm not getting any error. it is fetching max date data also. we don't need max date data.

Re: Incremental load issue

kaushiknsolanki — Thu, 12 Oct 2017 07:28:42 GMT

Try this.

let date_modified = Dayend(Peek('maxdate',0,'Date'));

Regards,

Kaushik Solanki

Re: Incremental load issue

Anonymous — Thu, 12 Oct 2017 08:54:06 GMT

Here the Createddate field is a time stamp.

example:

9/27/2017 10:23:12 AM

Re: Incremental load issue

kaushiknsolanki — Thu, 12 Oct 2017 09:20:35 GMT

Try the above expression I gave.

Regards,

Kaushik Solanki

Re: Incremental load issue

Anonymous — Thu, 12 Oct 2017 12:03:08 GMT

Hi Koushik,

Thanks for your reply. we are facing similar issue as you mentioned but we are loading data hourly basis not daily basis.

Thanks,

Chandra

Re: Incremental load issue

Anonymous — Mon, 16 Oct 2017 07:37:46 GMT

Hi,

if anyone knows please answer this.

Re: Incremental load issue

srivastalans — Mon, 16 Oct 2017 09:14:14 GMT

Hi Chandra,

First store the Database value as it is in Incremental_EMP QVD, then use concatenate function as mentioned below

Incremental_EMP:

SQL SELECT

EName, Empid, CreatedDate

FROM "QV_DB".db.Employee;

STORE Incremental_EMP into [..\QVDs\Employee.qvd] (qvd);

Try the below code,

Date:

LOAD max(Date(Timestamp(CreatedDate,'MM/DD/YYYY'))) as maxdate;

SQL SELECT

CreatedDate

FROM "QV_DB".db.Employee;

let vDate_Modified = Num(Date#(Peek('maxdate',0,'Date'),'MM/DD/YYYY'));

Incremental_EMP:

SQL SELECT

EName, Empid, CreatedDate

FROM "QV_DB".db.Employee

where Num(Date#(Timestamp(CreatedDate,'MM/DD/YYYY'),'MM/DD/YYYY')) >'$(vDate_Modified)';

Concatenate (Incremental_EMP)

LOAD EName, Empid, CreatedDate

FROM [..\QVDs\Employee.qvd] (qvd)

Where not Exists(Empid);

Inner Join (Incremental_EMP)

SQL SELECT Empid

FROM "QV_DB".db.Employee;

STORE Incremental_EMP into [..\QVDs\Employee.qvd] (qvd);

Re: Incremental load issue

qliksus — Mon, 16 Oct 2017 09:36:04 GMT

Did you try with the timestamp function like the below

SQL SELECT

EName, Empid, CreatedDate

FROM "QV_DB".db.Employee

where CreatedDate > timestamp( timestamp#('$(date_modified)' ,'MM/DD/YYYY HH:MM:SS'),,'MM/DD/YYYY HH:MM:SS') ;

Re: Incremental load issue

Anonymous — Mon, 16 Oct 2017 09:56:47 GMT

Hi Susant,

where CreatedDate > timestamp( timestamp#('$(date_modified)' ,'MM/DD/YYYY HH:MM:SS'),,'MM/DD/YYYY HH:MM:SS') ;

is throwing error because here we are loading data from DB. please suggest me to write this in SQL.

Re: Incremental load issue

qliksus — Mon, 16 Oct 2017 12:15:29 GMT

Put the same thing in the variable you are storing the date and use the query

let date_modified = timestamp( timestamp#(Peek('maxdate',0,'Date'), ,'MM/DD/YYYY HH:MM:SS'),,'MM/DD/YYYY HH:MM:SS') ;

SQL SELECT

EName, Empid, CreatedDate

FROM "QV_DB".db.Employee

where CreatedDate > '$(date_modified)'

Btw I have one more question does your employee table has data < date_modified

Re: Incremental load issue

Anonymous — Wed, 18 Oct 2017 12:30:56 GMT

Hi All,

Thanks for your replies.

Finally I got the solution using below statement.

=timestamp(Createddate,'MM/DD/YYYY hh:mm:ss.fff')

Thanks,

Chandra