topic Re: Limit Data until Previous Month in QlikView

Limit Data until Previous Month

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

Hello,

I have the following script:

DEMO:

LOAD * INLINE [

PERIOD, QTY

201701, 200

201702, 300

201703, 250

201704, 150

201705, 400

201706, 500

201707, 600

201708, 450

201709, 100

201710, 450

201711, 800

201712, 340

201801, 500

201802, 450

201803, 425

201804, 55

];

FINAL:

LOAD

PERIOD,

QTY

RESIDENT TABLE

// WHERE PERIOD < DATE(TODAY(),'YYYYMM');

DROP TABLE DEMO;

What I need is to limit field PERIOD to bring data only from Last Month (closed). In this case, we need to show until 201803.

But when it's, for example, 05/15/2018, it should bring data until 201804.

(What I've tried is not working).

Do you know how to do that?

Thank you!!!

Re: Limit Data until Previous Month

Anil_Babu_Samineni — Wed, 11 Apr 2018 14:00:05 GMT

Perhaps this?

FINAL:

LOAD

PERIOD,

QTY

RESIDENT TABLE

WHERE PERIOD <= Date(MonthName(Today(),-1), 'YYYYMM');

DROP TABLE DEMO;

Re: Limit Data until Previous Month

microwin88x — Wed, 11 Apr 2018 14:02:54 GMT

Hello, I've tried it but it doesn't work!

Re: Limit Data until Previous Month

sunny_talwar — Wed, 11 Apr 2018 14:05:54 GMT

Try this

FINAL:

LOAD

PERIOD,

QTY

RESIDENT TABLE

Where Date#(PERIOD, 'YYYYMM') < MonthStart(Today());

DROP TABLE DEMO;

Re: Limit Data until Previous Month

Anil_Babu_Samineni — Wed, 11 Apr 2018 14:07:47 GMT

PFA

Re: Limit Data until Previous Month

lcontezini — Wed, 11 Apr 2018 14:16:08 GMT

You are using exactly the same fields in each load statement, so you should use "NoConcatenate". Also, the PERIOD and date(today()) formats must be the same.

Try this:

DEMO:

LOAD * INLINE [

PERIOD, QTY

201701, 200

201702, 300

201703, 250

201704, 150

201705, 400

201706, 500

201707, 600

201708, 450

201709, 100

201710, 450

201711, 800

201712, 340

201801, 500

201802, 450

201803, 425

201804, 55

];

FINAL:

NoConcatenate

LOAD

PERIOD,

QTY

RESIDENT DEMO

WHERE PERIOD <> right(DATE(TODAY(),'YYYYMM'),6);

DROP TABLE DEMO;