Sorting Data - First sorted value - Pulling corresponding Text Field

[#1]

Hi

I am struggling putting together a summary table for the Base set of data below.

Year	Team	Count
2011	A	5
2011	B	6
2011	C	4
2011	D	9
2012	A	8
2012	B	2
2012	C	4
2012	D	5

I want the summary table to look like the one below.

Year	Team	Count
2011	C	4
2012	B	2

The table displays the Min count per year and the corresponding teams to that min count. The part i am struggling with is pulling though the corresponding teams. I have tried first sorted value but that doesn't seem to work I need a formula that pulls through the Team

Thanks for your help

[#2]

Please see the attached QVW, does that help?

For me (if I understood you correctly) FirstSortedValue did work, but that function depends on the sorting in your loadscript.

BTW: What I found a bit strange in my small sample app is that I couldn't sort with an inline loadscript...? It gave me a "garbage collection after load..." error (therefore a resident load). Is that normal?

[#3]

Hi

I am currently using the free version and i can't view any more files unfortunately. could you post the potential solution on the forum?

Regards

James

[#4]

No problem of course:

load script:

base:
LOAD * INLINE [
Year, Team, Count
2011, A, 5
2011, B, 6
2011, C, 4
2011, D, 9
2012, A, 8
2012, B, 2
2012, C, 4
2012, D, 5
]
;
NoConcatenate
load * Resident base order by Count;
drop table base;

Expression for your chart

FirstSortedValue( Team,Count)

Dimension is Year

Let me know if it worked...

[#5]

Can you provide a solution as an expression.

The count is likely to change based on different selections made on the solution. The suggestion you have made suggests the count remains static

James Makepeace

MI Analyst

Southern Rock Insurance

Tel: 01454635844

[#6]

possible sugggestion.

to calculate the min count field i use this expression.

Min({$<[Insurer]-={'Asda OE', 'eCar'}>} [Premium])

is there another expression which works by saying return [Team] WHERE [Premium]=

Min({$<[Insurer]-={'Asda OE', 'eCar'}>} [Premium])

[#7]

Hi miteamgib,

You have to select the minimum, use the group by clause and use also the left join.

See the attached.

Regards,

Zakaria Mourchid

[#8]

Hi

i am using the feee version. i can't access your attachemnts

Regards

J

[#9]

Hi

i am using the feee version. i can't access your attachemnts

Regards

J

[#10]

His option is:

// *****  Data Source  ***************************
Data:
LOAD * INLINE [
Year, Team, Count
2011,  A,   5
2011, B,   6
2011, C,   4
2011, D,   9
2012, A,   8
2012, B,   2
2012, C,   4
2012, D,   5
];
/*
Sort:
load
Year,
Team,
max(Count)
Resident Data Group By Year, Team;
*/
Sort:
load
Year,
min(Count) as Count
Resident Data
group by Year;

left Join

LOAD
Year,
Count,
Team
Resident Data;


drop table Data;

But if I understand you correctly, the calculation of your count is derived from other fields, and that may be the complexity right? To solve that, I need more information on that I'm afraid...