topic Re: months! in QlikView

months!

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

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.

Re: months!

tresB — Tue, 27 Jun 2017 09:02:02 GMT

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)

Re: months!

ahaahaaha — Tue, 27 Jun 2017 09:03:20 GMT

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

];

Re: months!

Kushal_Chawda — Tue, 27 Jun 2017 09:04:37 GMT

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;