topic Re: Date Extraction Script in QlikView

Date Extraction Script

mario-sarkis — Fri, 04 Sep 2015 06:30:12 GMT

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

Re: Date Extraction Script

jonathandienst — Fri, 04 Sep 2015 06:38:37 GMT

And your question is?

Re: Date Extraction Script

tresB — Fri, 04 Sep 2015 06:42:55 GMT

May be like:

Load

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

Re: Date Extraction Script

sasiparupudi1 — Fri, 04 Sep 2015 06:51:27 GMT

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;

Re: Date Extraction Script

sunny_talwar — Fri, 04 Sep 2015 06:54:45 GMT

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

Re: Date Extraction Script

qlikmsg4u — Fri, 04 Sep 2015 07:04:18 GMT

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;

Re: Date Extraction Script

mario-sarkis — Fri, 04 Sep 2015 14:15:18 GMT

hi Sunny i need it within the script

Re: Date Extraction Script

mario-sarkis — Fri, 04 Sep 2015 14:41:47 GMT

hi sunny neet to extract within the script ,

Thank you

Re: Date Extraction Script

maxgro — Fri, 04 Sep 2015 17:38:55 GMT

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;

Re: Date Extraction Script

MarcoWedel — Fri, 04 Sep 2015 21:45:04 GMT

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