Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
arethaking
Creator II
Creator II

Monthly Data Load

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

];

1 Solution

Accepted Solutions
jagan
MVP
MVP

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.

View solution in original post

7 Replies
jagan
MVP
MVP

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.

View solution in original post

arethaking
Creator II
Creator II
Author

Hi Jagan,

Seems it is not working.

Please help to check with the attached data. !

jagan
MVP
MVP

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
Creator II
Creator II
Author

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
MVP
MVP

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
Creator II
Creator II
Author

SELECT

*

FROM TableName

WHERE MONTH >= '01Oct16';

This is not working.

jagan
MVP
MVP

TRY this

SELECT

*

FROM TableName

WHERE MONTH >= '01-OCT-2016'

Or  try changing to other formats.

Regards,

Jagan.