topic Re: How to get max date from script depending upon current selection of calender date in QlikView

How to get max date from script depending upon current selection of calender date

Tue, 28 Apr 2015 06:48:27 GMT

Hello All,

I have Batch date from script but I want to pull latest date from script depending upon current selection. Please refer the following ex:

EX:

Header 1	Header 2	Header 3	Header 4
MATNR	KEY	BatchDate	Qty
1	123	22/1/2014	20
1	456	18/2/2015	20
1	789	24/4/2015	20

In above example : MATNR 1: I have 3 keys and 3 dates but with same qty. Now In calender I have selcted 2015 Apr.

So in that case I want only 1 row that is last row as it is nearer to my selection date.

How to handle this in script side only, so How to write a script for that.

Re: How to get max date from script depending upon current selection of calender date

Gysbert_Wassenaar — Tue, 28 Apr 2015 09:24:58 GMT

Current selections are not available during the load. So there are no current selections that can be used in the script.

Re: How to get max date from script depending upon current selection of calender date

Tue, 28 Apr 2015 09:29:39 GMT

use below script it will give max date

load

MATNR,

Qty

max(BatchDate) as BatchDate resident table group byMATNR,Qty;

and let me know if it is working or not or else provide data

Thanks

Manju

Re: How to get max date from script depending upon current selection of calender date

Tue, 28 Apr 2015 09:33:05 GMT

cane we handle it in expression side

Re: How to get max date from script depending upon current selection of calender date

marcus_sommer — Tue, 28 Apr 2015 10:43:00 GMT

Another method to fetch the min/max value from a field: “Fastest” Method to Read max(field) From a QVD | Qlikview Cookbook.

- Marcus

Re: How to get max date from script depending upon current selection of calender date

NareshGuntur — Tue, 28 Apr 2015 11:44:04 GMT

Please check this and let me know if you have any questions.

Cheers,

Naresh

Re: How to get max date from script depending upon current selection of calender date

Tue, 28 Apr 2015 12:40:11 GMT

It will work for irrespectiove of current selection.

It means in your solution it will give me 25 Apr 2015 but if i select Jan 2015 then in that case I want first record i.e,

22 Jan 2015

Re: How to get max date from script depending upon current selection of calender date

NareshGuntur — Tue, 28 Apr 2015 18:00:14 GMT

Did you try the document which I have given? It is working fine.

Cheers,

Naresh

Re: How to get max date from script depending upon current selection of calender date

Wed, 29 Apr 2015 10:00:55 GMT

Hello Naresh,

I didn't get any document can you send it again.

Re: How to get max date from script depending upon current selection of calender date

NareshGuntur — Wed, 29 Apr 2015 10:03:36 GMT

Please check this

Re: How to get max date from script depending upon current selection of calender date

Wed, 29 Apr 2015 10:11:06 GMT

No Naresh ,

still i cant get any attachment.

Re: How to get max date from script depending upon current selection of calender date

NareshGuntur — Wed, 29 Apr 2015 10:17:53 GMT

Let me post it here

The data is taken from your initial post

//Script start

Test1:

LOAD * Inline [

MATNR, KEY, BatchDate, Qty

1, 123, 22/1/2014, 20

1, 456, 18/2/2015, 20

1, 789, 24/4/2015, 20

];

Test2:

LOAD

Year(date#(BatchDate,'DD/MM/YYYY')) as Year,

Month(date#(BatchDate,'DD/MM/YYYY')) as Month

Resident Test1;

set newdate = "date(MakeDate(Year,Month),'DD/MM/YYYY')";

//Script end

Expression in chart: sum(if(Month=month(date#(BatchDate,'DD/MM/YYYY')) and Year = Year(date#(BatchDate,'DD/MM/YYYY')), Qty))

Re: How to get max date from script depending upon current selection of calender date

Wed, 29 Apr 2015 10:26:36 GMT

table

;-----

tab:

A B

1 1/12/2012

2 2/12/2013

3 3/12/2014

tab1:

load * resident tab(table name) order by B ( B is field of above table) ;

let maxdate = peek( ' B (field name ) ', 0 (position of field value ) , ' tab1 (table name ');

Re: How to get max date from script depending upon current selection of calender date

Wed, 29 Apr 2015 10:28:31 GMT

Hello Naresh,

Thankx for solution.

But in your solution, you did batch date as a calender date but I can't do like that because my Calender is depending upon posting date.

So I cant't make batchdate as calender date.

Can you have another solution.

Re: How to get max date from script depending upon current selection of calender date

NareshGuntur — Wed, 29 Apr 2015 10:39:22 GMT

You need not use Batch Date for Calendar date. As I didn't have calendar for demo, I just used Batch Date. You can use your normal calendar for Year and Month.

Best Regards,

Naresh

Re: How to get max date from script depending upon current selection of calender date

Wed, 29 Apr 2015 10:55:05 GMT

Hello Naresh,

Ya it's fine but I have to handle 2 more conditions in this solution.

1) If I have 2 date in same month suppose in Feb I have 2 date i.e, 18 Feb and 21 Feb then i want only 21 Feb as it is max date.

2) If I have selected 2015 Jan and there is no data for Jan then it should go to backtrack i.e, go to previous month and select max date from previous month date as batch date.

So can we handle these condition in your solution.

If yes can you provide me solution.