Addmonths functions while pulling data from QVD?

[qlikuser222]

Hi All,

I am trying to load data from a QVD where records are equal to one m,onth less than max (date)

Please go through the attached QVW, which explains my requirement.

Thanks

[sunny_talwar]

Max date is 10/13/2016 and one month before that date would 9/13/2016? Why are you looking to pull 9/9/2016? Is it because it is the closest date to the Max Date - 1? So you would always want to pull one record in total? I think you will have to provide some more details, before one of us can help you out here

[rubenmarin]

Hi Nicholas, try with this script:

data:

LOAD ID, Date#(Date, 'MM/DD/YYYY') as Date, Status Inline [

ID, Date, Status

1,7/12/2016,Y

1,8/10/2016,Y

1,9/09/2016,Y

1,10/12/2016,Y

2,7/18/2016,Y

2,8/22/2016,Y

2,9/02/2016,Y

2,10/13/2016,Y];

MaxDate_LastMonth:

LOAD AddMonths(Max(Date), -1) as MaxDate_LastMonth

Resident data;

LET vMinDate = MonthStart(Peek('MaxDate_LastMonth'));

LET vMaxDate = MonthEnd(Peek('MaxDate_LastMonth'));

MaxDateToFilter:

LOAD Max(Date) as DateToFilter

Resident data Where Date>='$(vMinDate)' and Date<='$(vMaxDate)';

DROP Tables data, MaxDate_LastMonth;

// DateToFilter contains the date, you can extract to a variable for use in 'where condition' with:

// LET vDateToFilter = Peek('DateToFilter');

[qlikuser222]

HI Sunny,

Sorry about the insuffie QVW.

My example data is

load * inline [

ID, Date, Status1,Status2

1,7/30/2016,Y,Y

1,8/30/2016,Y,N

1,9/30/2016,Y,Y

1,10/30/2016,Y,Y

2,7/30/2016,Y,N

2,8/30/2016,N,Y

2,9/30/2016,Y,Y

2,10/30/2016,Y,Y];

The above data is in QVD

i want load only

1,9/30/2016,Y,Y and 2,9/30/2016,Y,Y into QVW from the above QVD data. (records which are associated to  one month less than max month)

Can you please help me ?

Thanks

[sunny_talwar · Accepted solution]

May be something along these lines:

Table:

load * inline [

ID, Date, Status1,Status2

1,7/30/2016,Y,Y

1,8/30/2016,Y,N

1,9/30/2016,Y,Y

1,10/30/2016,Y,Y

2,7/30/2016,Y,N

2,8/30/2016,N,Y

2,9/30/2016,Y,Y

2,10/30/2016,Y,Y];

MaxLoad:

LOAD AddMonths(Max(Date), -1) as ReqDate

Resident Table;

LET vReqDate = Num(Peek('ReqDate'));

TRACE $(vReqDate);

DROP Table MaxLoad;

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where Date = $(vReqDate);

DROP Table Table;