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

[arvind_patil]

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

[Kushal_Chawda]

which is the underlying database? Oracle or Sql server?

[avinashelite]

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

[Anonymous]

Sql server

[kaushiknsolanki]

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

[Anonymous]

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

[kaushiknsolanki]

Try this.

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

Regards,

Kaushik Solanki

[Anonymous]

Here the Createddate field is a time stamp.

example:

9/27/2017 10:23:12 AM

[kaushiknsolanki]

Try the above expression I gave.

Regards,

Kaushik Solanki