In which year which fruit has crossed more than $1000 sales

[vidyasagar159]

Hi All,

I need some help. From the below sample data. I would like to identify only the records of the fruits that crossed the $1000 sales for the first time.

Example: Grapes has crossed $1000 first time in 2015. I just want to show that one record I do not want to see all the years of the fruits which crossed greater than $1000.

Fruits	Year	sales
Apples	2015	$500
Apples	2016	$700
Apples	2017	$1,100
Grapes	2015	$1,200
Grapes	2016	$600
Grapes	2017	$1,100
Mangoes	2015	$1,000
Mangoes	2016	$1,300
Mangoes	2017	$1,200

Thanks,

-Vidya

[sunny_talwar]

So, you want to see 1 row per each fruit? 2015 for Grapes, 2017 for Apples, and 2015 for Mangoes?

[vidyasagar159]

Thats correct.

[sunny_talwar · Accepted solution]

Check this out

Dimension

Fruits

Expressions

Min({<sales = {'>=1000'}>}Year)
FirstSortedValue({<sales = {'>=1000'}>} sales, Year)

[vidyasagar159]

Awesome this is exactly what I am looking for. By any chance can we achieve the same in the load script editor? 

[sunny_talwar]

So, you mean that bring in only 3 rows and drop all other rows?

[vidyasagar159]

Yes, That's correct.

[sunny_talwar · Accepted solution]

Try this script

Table:
LOAD * INLINE [
    Fruits, Year, sales
    Apples, 2015, 500
    Apples, 2016, 700
    Apples, 2017, 1100
    Grapes, 2015, 1200
    Grapes, 2016, 600
    Grapes, 2017, 1100
    Mangoes, 2015, 1000
    Mangoes, 2016, 1300
    Mangoes, 2017, 1200
];

Right Join (Table)
LOAD Fruits,
	 Min(Year) as Year
Resident Table
Where sales >= 1000
Group By Fruits;

[vidyasagar159 · Accepted solution]

Thanks for all your help today.