topic Re: How to make a filter on Month in the load Script ? in QlikView

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

bouderbc — Mon, 25 Sep 2017 08:27:56 GMT

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:

Load Script:

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 ?

sunny_talwar — Mon, 25 Sep 2017 08:32:37 GMT

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 ?

prieper — Mon, 25 Sep 2017 08:35:55 GMT

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 ?

bouderbc — Mon, 25 Sep 2017 08:48:04 GMT

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 ?

bouderbc — Mon, 25 Sep 2017 08:57:37 GMT

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 ?

Anil_Babu_Samineni — Mon, 25 Sep 2017 09:00:29 GMT

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 ?

PrashantSangle — Mon, 25 Sep 2017 09:11:04 GMT

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

Regards,

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

Anil_Babu_Samineni — Mon, 25 Sep 2017 09:14:01 GMT

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 ?

PrashantSangle — Mon, 25 Sep 2017 09:28:25 GMT

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 ?

bouderbc — Mon, 25 Sep 2017 09:36:01 GMT

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 ?

Anil_Babu_Samineni — Mon, 25 Sep 2017 09:41:05 GMT

I am referring this?

LOAD * Inline [

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

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

prieper — Mon, 25 Sep 2017 10:11:26 GMT

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