Get the first and last value of each year

[brunolelli87]

Dears,

How can I collect the first and last value of VDAF of each year?
My intention is to calculate the difference of VDAF between the last and the first day of each year!

STCK	PERI (DD/MM/YYYY)	VDAF
AZUL4	01/01/2015	10.15
AZUL4	12/05/2015	12.22
AZUL4	08/08/2015	14.00
AZUL4	13/11/2015	13.28
AZUL4	27/12/2015	14.33
AZUL4	02/01/2016	15.01
AZUL4	05/08/2016	14.22
AZUL4	09/12/2016	16.33
AZUL4	01/03/2017	16.44
AZUL4	01/09/2017	16.50
AZUL4	01/12/2017	16.55

I'm expecting to get the following numbers:
2015: 14.33 - 10.15 = 01.18
2016: 16.33 - 15.01 = 01.32
2017: 16.55 - 16.44 = 01.11

How can I do that?

Thanks

[Taoufiq_Zarra]

Maye be :

Data:
LOAD *,Year(Date(PERI)) as YearPERI INLINE [
    STCK, PERI, VDAF
    AZUL4, 01/01/2015, 10.15
    AZUL4, 12/05/2015, 12.22
    AZUL4, 08/08/2015, 14.00
    AZUL4, 13/11/2015, 13.28
    AZUL4, 27/12/2015, 14.33
    AZUL4, 02/01/2016, 15.01
    AZUL4, 05/08/2016, 14.22
    AZUL4, 09/12/2016, 16.33
    AZUL4, 01/03/2017, 16.44
    AZUL4, 01/09/2017, 16.50
    AZUL4, 01/12/2017, 16.55
];

output:

load YearPERI,FirstSortedvalue(VDAF,-Date(PERI)) as Last,FirstSortedvalue(VDAF,Date(PERI)) as First resident Data group by YearPERI;

drop table Data;

[brunolelli87]

Thanks for your help...

I forgot to mention that I would like to have that value per STCK... In the example above I just mentioned AZUL4, but I have lots of other STCKs, like GOLL4, GGBR4... and so on...

And is it possible to do it using expression and set analysis?

Thanks

[brunolelli87]

Any idea?

[brunolelli87]

Hello guys,

Any other idea? It's very important to me!

[Saravanan_Desingh]

One solution is.

SET DateFormat='DD/MM/YYYY';

tab1:
LOAD *, Year(PERI) As Yr INLINE [
    STCK, PERI, VDAF
    AZUL4, 1/1/2015, 10.15
    AZUL4, 12/5/2015, 12.22
    AZUL4, 8/8/2015, 14
    AZUL4, 13/11/2015, 13.28
    AZUL4, 27/12/2015, 14.33
    AZUL4, 2/1/2016, 15.01
    AZUL4, 5/8/2016, 14.22
    AZUL4, 9/12/2016, 16.33
    AZUL4, 1/3/2017, 16.44
    AZUL4, 1/9/2017, 16.5
    AZUL4, 1/12/2017, 16.55
    GOLL4, 1/1/2015, 10.15
    GOLL4, 12/5/2015, 12.22
    GOLL4, 8/8/2015, 14
    GOLL4, 13/11/2015, 13.28
    GOLL4, 27/12/2015, 14.33
    GOLL4, 2/1/2016, 15.01
    GOLL4, 5/8/2016, 14.22
    GOLL4, 9/12/2016, 16.33
    GOLL4, 1/3/2017, 16.44
    GOLL4, 1/9/2017, 16.5
    GOLL4, 1/12/2017, 16.55
    GGBR4, 1/1/2015, 10.15
    GGBR4, 12/5/2015, 12.22
    GGBR4, 8/8/2015, 14
    GGBR4, 13/11/2015, 13.28
    GGBR4, 27/12/2015, 14.33
    GGBR4, 2/1/2016, 15.01
    GGBR4, 5/8/2016, 14.22
    GGBR4, 9/12/2016, 16.33
    GGBR4, 1/3/2017, 16.44
    GGBR4, 1/9/2017, 16.5
    GGBR4, 1/12/2017, 16.55
];

tab2:
LOAD RowNo() As RowID, Yr, STCK, FirstSortedValue(VDAF, PERI) As VDAF1, FirstSortedValue(VDAF, -PERI) As VDAF2
Resident tab1
Group By STCK, Yr
;

tab3:
LOAD Yr, STCK, (VDAF2-VDAF1) As [Diff],VDAF1,VDAF2
Resident tab2
;

Drop Table tab1, tab2;

[Saravanan_Desingh]