topic Monthly Data Load in QlikView

Monthly Data Load

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

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

];

Re: Monthly Data Load

jagan — Mon, 17 Oct 2016 03:58:39 GMT

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.

Re: Monthly Data Load

arethaking — Mon, 17 Oct 2016 06:10:22 GMT

Hi Jagan,

Seems it is not working.

Please help to check with the attached data. !

Re: Monthly Data Load

jagan — Mon, 17 Oct 2016 06:48:00 GMT

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.

Re: Monthly Data Load

arethaking — Mon, 17 Oct 2016 07:12:19 GMT

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

Re: Monthly Data Load

jagan — Mon, 17 Oct 2016 07:24:50 GMT

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.

Re: Monthly Data Load

arethaking — Mon, 17 Oct 2016 07:59:05 GMT

SELECT

FROM TableName

WHERE MONTH >= '01Oct16';

This is not working.

Re: Monthly Data Load

jagan — Mon, 17 Oct 2016 08:21:43 GMT

TRY this

SELECT

FROM TableName

WHERE MONTH >= '01-OCT-2016'

Or try changing to other formats.

Regards,

Jagan.