Select values against the first(Min) Date

[psk180590]

Hello All,

I have the following data:

Article Code	Date	Cost
1	01.01.2012	3
1	01.02.2012	5
1	01.06.2012	15
2	01.01.2012	6
3	01.01.2012	30
3	01.09.2012	23
1	01.01.2013	10
1	01.07.2013	12

My Date field is in 'DD.MM.YYYY' Format.

I would like to have the Cost of each Article against the Min of Date.

My expected result is:

Article Code	Year	Cost
1	2012	3
2	2012	6
3	2012	30
1	2013	10

Tried with First() Function but, somehow the Years keep repeating.

TIA!!

[Anil_Babu_Samineni]

Script or Front end?

[psk180590]

Script

[Anil_Babu_Samineni]

Try this?

SET DateFormat='DD.MM.YYYY';

Table:

LOAD *, Year(Date) as Year Inline [

Article Code, Date, Cost

1, 01.01.2012, 3

1, 01.02.2012, 5

1, 01.06.2012, 15

2, 01.01.2012, 6

3, 01.01.2012, 30

3, 01.09.2012, 23

1, 01.01.2013, 10

1, 01.07.2013, 12

];

Right Join(Table)

LOAD Min(Date) as Date, Year, [Article Code] Resident Table Group By Year, [Article Code];

[tresesco · Accepted solution]

Try like:

SET DateFormat='DD.MM.YYYY';

t1:

Load * Inline [

Article Code, Date, Cost

1, 01.01.2012, 3

1, 01.02.2012, 5

1, 01.06.2012, 15

2, 01.01.2012, 6

3, 01.01.2012, 30

3, 01.09.2012, 23

1, 01.01.2013, 10

1, 01.07.2013, 12]

;

t2:

Load

Year(Date) as Year,

*

Resident t1;

t3:

Load

Year,

"Article Code",

FirstSortedValue(Cost, Date) as Cots

Resident t2 Group By Year,"Article Code";

Drop table t1, t2;