How to find immediate and immediate-1 Dates in Qlikview if i am getting random dates in data

[madhavi_rajawat]

Hi All,

I have requirement to  find out immediate and immediate-1 Dates in Qlikview if i am getting random dates.

For Example: I have  fields MaterialID ,Batch_0,Vigor%,VigorTestDate,Germination%,GerminationTestDate and i am getting random date.

i have requirement to find out VigorTestDate and  GerminationTestDate  Immediate and Immediate -1 dates on basic of Batch selection  that means if i select batch 11 then in VigorTestDate i want 17-07-2017 and 15-03-2016 and in GerminationTestDate i want 26-09-2017 and  22-08-2017.

i have attached sample application also for reference

i want solution how i can achieve this in backed and front end  as well

Note :I am using personal edition of Qlikview so please post  sample solution in notepad so i can view solution and implement same in my Qlikview .

TAB:

load *inline

[

MaterialID,Batch_0,Vigor%,VigorTestDate,Germination%,GerminationTestDate

1,11,90,12-03-2013,29,16-02-2917

1,11,81,15-03-2016,25,22-08-2017

1,11,88,17-08-2011,40,02-04-2017

1,11,14,17-07-2017,25,26-09-2017

2,12,50,18-02-2014,22,04-03-2014

2,12,12,18-04-2017,25,12-07-2015

2,12,23,08-11-2016,30,14-08-2016

2,12,45,25-12-2017,31,22-08-2016

3,13,20,01-02-2017,20,15-05-2011

3,13,30,11-09-2017,20,16-06-2015

3,13,25,10-05-2017,10,18-07-2017

3,13,25,13-10-2017,30,22-12-2017

];

[Gysbert_Wassenaar · Accepted solution]

Script:

LOAD

     Batch

     Max(VigorTestDate) as MaxVigorTestDate,

     Max(VigorTestDate,2) as [MaxVigorTestDate-1],

     Max(GerminationTestDate) as MaxGerminationTestDate ,

     Max(GerminationTestDate,2) as [GerminationTestDate-1]

FROM

     ...source...

GROUP BY

     Batch

Frond end:

Use Batch as dimension and the below expressions:

     Max(VigorTestDate)

     Max(VigorTestDate,2)

     Max(GerminationTestDate)

     Max(GerminationTestDate,2)