Monthly Data Load

[arethaking]

Hi All,

I am loading data incrementally.

My data is on monthly level in database and MONTH column as character as 01JAN2016,01FEB2016,01MAR2016 and so on.

I want to load the data only latest month data for each load.If I load twice in the same month, data should be deleted only for that month and load latest data.

I am using this code.

Please advise if it wrong.

LET lastReloadTime = date(MONTHSTART(today()),'DDMMMYYYY');

//LET lastReloadTime = num(date(ReloadTime(),'DDMMMYYYY'));

SALES:

LOAD EMPNO,

     ENAME,

     SALES,

     DATE(MONTH,'DDMMMYY') AS MONTH

FROM

Incremental.xlsx

(ooxml, embedded labels, table is DATA)

where MONTH>='$(lastReloadTime)';

CONCATENATE

LOAD EMPNO,

     ENAME,

     SALES,

     MONTH

FROM

SALES.QVD

(qvd)

WHERE NOT exists (EMPNO);

STORE SALES into SALES.QVD(QVD);

Data:

Load * line [

EMPNO,ENAME,SALES,MONTH

1,E1,10,01Jan16

2,E2,5,01Jan16

3,E3,115,01Jan16

4,E4,15,01Jan16

5,E5,18,01Jan16

6,E6,20,01Jan16

7,E7,23,01Jan16

8,E8,25,01Jan16

9,E9,28,01Jan16

10,E10,30,01Oct16

11,E11,33,01Oct16

12,E12,35,01Oct16

13,E13,38,01Jan16

14,E14,40,01Jan16

15,E15,43,01Nov16

16,E16,45,01Nov16

];

[jagan · Accepted solution]

Hi,

Try this

LET lastReloadTime = DATE(MONTHSTART(ReloadTime()),'DDMMMYYYY');

SALES:

LOAD EMPNO,

     ENAME,

     SALES,

     DATE(MONTH,'DDMMMYY') AS MONTH

FROM

Incremental.xlsx

(ooxml, embedded labels, table is DATA)

where MONTH >= '$(lastReloadTime)';

CONCATENATE

LOAD EMPNO,

     ENAME,

     SALES,

     MONTH

FROM

SALES.QVD

(qvd)

WHERE MONTH < '$(lastReloadTime)';

STORE SALES into SALES.QVD(QVD);

EXIT Script;

Hope this helps you.

Regards,

jagan.

[arethaking]

Hi Jagan,

Seems it is not working.

Please help to check with the attached data. !

[jagan]

Hi,

There is no data for current month, may be that is the reason I think you are not getting the solution.  I just added a dummy record for October.  Attached the QVD, QVW file for your reference.

Regards,

Jagan.

[arethaking]

Thank You Jagan.

My actual data source is a database.

Will the below syntax work ?

.

.

.

FROM

Incremental.xlsx

(ooxml, embedded labels, table is DATA)

where MONTH >= '$(lastReloadTime)';

[jagan]

How you are filtering in your select query?  Is below query works inDB?

SELECT

*

FROM TableName

WHERE MONTH >= '01Oct16';

Is this query working in DB?

Regards,

Jagan.

[arethaking]

SELECT

*

FROM TableName

WHERE MONTH >= '01Oct16';

This is not working.

[jagan]

TRY this

SELECT

*

FROM TableName

WHERE MONTH >= '01-OCT-2016'

Or  try changing to other formats.

Regards,

Jagan.