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

[#1]

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.

[#2]

No Naresh ,

still i cant get any attachment.

[NareshGuntur]

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))

[#4]

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 ');

[#5]

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.

[NareshGuntur]

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

[#7]

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.