Date Extraction Script

[mario-sarkis]

Hi ,

I have this type of table

ID        DATE(monthYear)          Balance

1          JUL 2015                       10000

1          JUN 2015                       20000

2          APr 2015                       10000

3          JUN 2015                       13000

3           MAY 2015                     14000

4           JUL 2015                      10000

5         JUN 2015                        15000

6           APr2015                        10000

4           JUL 2015                      10400

I only Want to Extract with Condition of thath Date=Addmonths(Max(Date)-1) in this example i will get only JUN 2015 Data

ID        DATE(monthYear)          Balance

1          JUN 2015                       20000

3          JUN 2015                       13000

5         JUN 2015                        15000

Thank you

[jonathandienst]

And your question is?

[tresesco]

May be like:

Load

          *

From <> Where Date>=MonthStart(Max(Date),-1) ;

[sasiparupudi1]

t1:

load ID, date(date#(DATE,'MMM YYYY'),'MMM YYYY') as DATE,Balance;

load * Inline

[

ID,DATE ,  Balance

1,  JUL 2015,      10000

1,  JUN 2015,       20000

2,  APr 2015,       10000

3,  JUN 2015,       13000

3,   MAY 2015,     14000

4,   JUL 2015,      10000

5, JUN 2015,15000

6,   APR 2015,10000

4,   JUL 2015,      10400

];

max:

load addmonths(max(DATE),-1) as maxDate Resident t1;

let vMaxDate=Peek('maxDate',0,'max');

NoConcatenate

final:

LOAD * Resident t1 where DATE='$(vMaxDate)';

drop Table t1;

[sunny_talwar]

You want to extract in within the script or in a front end chart?

[qlikmsg4u]

Test:

Load ID,

  Balance,

  Date#(monthYear,'MMM YYYY') as Date,

  Month(Date#(monthYear,'MMM YYYY')) as Month,

  Year(Date#(monthYear,'MMM YYYY')) as Year;

Load * Inline [

ID, monthYear,Balance

1, JUL 2015,10000

1, JUN 2015,20000

2, APr 2015,10000

3, JUN 2015,13000

3, MAY 2015,14000

4, JUL 2015,10000

5, JUN 2015,15000

6, APR 2015,10000

4, JUL 2015,10400

];

Temp:

LOAD

     Max(Month) as MaxMonth,

     Max(Year) as MaxYear

Resident Test;

LET vYear = floor(peek('MaxYear',0,'Temp'));

LET vMonth = floor(peek('MaxMonth',0,'Temp'));

NoConcatenate

Final:

Load * Resident Test Where Year = '$(vYear)' and Month = '$(vMonth)';

Drop Table Temp;

DROP Table Test;

[mario-sarkis]

hi Sunny i need it within the script

[mario-sarkis]

hi sunny neet to extract within the script ,

Thank you

[maxgro]

t1:

load ID, DATE, Balance;

load * Inline

[

ID,DATE ,  Balance

1,  JUL 2015,  10000

1,  JUN 2015,  20000

2,  APr 2015,  10000

3,  JUN 2015,  13000

3,  MAY 2015,  14000

4,  JUL 2015,  10000

5, JUN 2015, 15000

6,  APR 2015, 10000

4,  JUL 2015,  10400

];

inner join (t1)  LOAD

  upper(text(date(addmonths(max(date#(DATE,'MMM YYYY')),-1), 'MMM YYYY'))) as DATE

Resident t1;

[MarcoWedel]

Hi,

another solution could be:

table1:
LOAD ID,
    Date#([DATE(monthYear)],'MMM YYYY') as [DATE(monthYear)],
    Balance
INLINE [
    ID, DATE(monthYear), Balance
    1, JUL 2015, 10000
    1, JUN 2015, 20000
    2, APR 2015, 10000
    3, JUN 2015, 13000
    3, MAY 2015, 14000
    4, JUL 2015, 10000
    5, JUN 2015, 15000
    6, APR 2015, 10000
    4, JUL 2015, 10400
];

Right Join
LOAD AddMonths(Max([DATE(monthYear)]),-1) as [DATE(monthYear)]
Resident table1;

hope this helps

regards

Marco