11 Replies Latest reply: Sep 25, 2017 6:11 AM by Peter Rieper

# How to make a filter on Month in the load Script ?

How to make a filter on Month in the load Script ?

Hi Experts,

In my data , i have four month , but i must make a filter in the load script to load only the month before the last (-1)

Example:

Month:

My Request:  I wanna load only the Month before the last (-1) each time in the load script, so the Month 8 in this case

Tell me how to do to make a filter on the field Month

Thank you

Regards

Ali

• ###### Re: How to make a filter on Month in the load Script ?

So, essentially you don't want to load any data for the max month? Is this for only the current year or all the years?

• ###### Re: How to make a filter on Month in the load Script ?

Hi Sunny,

is only for the current Year , and in my Data i have always 4 months , and i have to load only the month before the last (Max)

So in this case Max is 09 = September , i want to load only 08= August

Each time when i load the new Data , i want to have only the  month (-1) before the last one (Max)

• ###### Re: How to make a filter on Month in the load Script ?

LOAD * FROM .... WHERE MONTH(MyDate) = MONTH(TODAY() -1);

If you need a logic over the end of the year, you may convert the monthes into rolling monthes,

i.e.

LOAD * FROM .... WHERE YEAR(MyDate) * 12 + MONTH(MyDate) = YEAR(TODAY() * 12 + MONTH(TODAY() -1);

• ###### Re: How to make a filter on Month in the load Script ?

Hi Peter ,

is only for the current Year , and in my Data i have always 4 months , and i have to load only the month before the last (Max)

So in this case Max is 09 = September , i want to load only 08= August

Each time when i load the new Data , i want to have only the  month (-1) before the last one (Max)

• ###### Re: How to make a filter on Month in the load Script ?

But then you may keep this variable:

Temp: LOAD MAX(MONTH(MyDate)) AS MaxMonth FROM ....;

LET vMaxMonth = PEEK('MaxMonth', 0, 'Temp');

DROP TABLE Month;

Data: LOAD * FROM .... WHERE MONTH(MyDate) = \$(vMaxMonth) -1;

Rolling month as before. You may need to cover for an error, if there is just one month ...

• ###### Re: How to make a filter on Month in the load Script ?

You need to create one variable to pick the Max Month value and then required to use Where condition using

Load * From Table Where Mois <= \$(Variable)-1

• ###### Re: How to make a filter on Month in the load Script ?

loveisfail I think you are suggesting >= \$(Variable)-1

Regards,

• ###### Re: How to make a filter on Month in the load Script ?

No, Because he need till last month data. If we give >=\$(Variable)-1 it loads only Current and Last months data i believe..

• ###### Re: How to make a filter on Month in the load Script ?

Opps my mistake I'm suggesting = \$(Variable)-1

because he wants only previous month data.

Regards,

• ###### Re: How to make a filter on Month in the load Script ?

Hi Anil,

Can you tell me All  the steps to  make the variable and where to put it in the Load

Thank You

• ###### Re: How to make a filter on Month in the load Script ?

I am referring this?

MonthField, SalesField

1, 10

2, 20

3, 30

4, 40

5, 50

6, 60

7, 70

8, 80

9, 90

10, 100

11, 110

12, 120

] Where MonthField = \$(Variable)-1; // You can change as your needed..

LET Variable = Peek('MonthField', -1);