Incremental load issue

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Hi,

if anyone knows please answer this.

[srivastalans]

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

[qliksus]

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

[Anonymous]

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.

[qliksus]

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

[Anonymous · Accepted solution]

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