Limit Data until Previous Month

[microwin88x]

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

[Anil_Babu_Samineni]

Perhaps this?

FINAL:

LOAD

PERIOD,

QTY

RESIDENT TABLE

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

DROP TABLE DEMO;

[microwin88x]

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

[sunny_talwar · Accepted solution]

Try this

FINAL:

LOAD

PERIOD,

QTY

RESIDENT TABLE

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

DROP TABLE DEMO;

[Anil_Babu_Samineni]

PFA

[lcontezini]

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;