months!

[raadwiptec]

my source is a an excel in this format

material  | quantity |  year | month

101  | 10 | 2017 | 06

102  | 11 | 2017 | 07

103  | 12 | 2017 | 08

104  | 13 | 2017 | 09

105  | 14 | 2017 | 10

107 | 15 |  2017 | 11

I need to have a load statement for the data only from the current month to the next 5 months.

[tresesco · Accepted solution]

load * Inline [

material  | quantity |  year | month

101  | 10 | 2017 | 06

102  | 11 | 2017 | 07

103  | 12 | 2017 | 08

104  | 13 | 2017 | 09

105  | 14 | 2017 | 10

107 | 15 |  2017 | 11

108 | 15 |  2017 | 12

109 | 15 |  2018 | 1

110 | 15 |  2018 | 2

] (delimiter is '|') where MakeDate(year,month)>=MonthStart(today()) and

  MakeDate(year,month)<=MonthStart(today(),5)

[ahaahaaha]

Hi,

LOAD*,

MakeDate(year, month) as Date

Where MakeDate(year, month)<=AddMonths(MonthStart(Today()), 5);

LOAD*Inline

[material  , quantity ,  year , month

101  , 10 , 2017 , 06

102  , 11 , 2017 , 07

103  , 12 , 2017 , 08

104  , 13 , 2017 , 09

105  , 14 , 2017 , 10

107 , 15 ,  2017 , 11

106 , 15 ,  2017 , 12

];

[Kushal_Chawda]

Data:

LOAD *,

          makedate(year ,month) as Date,

FROM table;

MinMaxDate:

LOAD max(Date) as Max,

          addmonths(max(Date) ,-5) as Min

Resident data;

let vMin= peek('Min',0,'MinMaxDate');

let vMax= peek('Max',0,'MinMaxDate');

drop table MinMaxDate;

Final:

noconcatenate

LOAD *

Resident Data

where floor(Date) >=$(vMin) and floor(Date)<=$(vMax);

drop table Data;