topic Re: Sorting Data - First sorted value - Pulling corresponding Text Field in QlikView

Sorting Data - First sorted value - Pulling corresponding Text Field

Fri, 06 Jul 2012 08:34:16 GMT

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

Re: Sorting Data - First sorted value - Pulling corresponding Text Field

Fri, 06 Jul 2012 08:48:37 GMT

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?

Re: Sorting Data - First sorted value - Pulling corresponding Text Field

Fri, 06 Jul 2012 08:50:40 GMT

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

Re: Sorting Data - First sorted value - Pulling corresponding Text Field

Fri, 06 Jul 2012 08:52:16 GMT

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...

Re: Sorting Data - First sorted value - Pulling corresponding Text Field

Fri, 06 Jul 2012 08:54:07 GMT

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

Re: Sorting Data - First sorted value - Pulling corresponding Text Field

Fri, 06 Jul 2012 08:57:30 GMT

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])

Re: Sorting Data - First sorted value - Pulling corresponding Text Field

Fri, 06 Jul 2012 09:02:01 GMT

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

Re: Sorting Data - First sorted value - Pulling corresponding Text Field

Fri, 06 Jul 2012 09:06:21 GMT

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

Regards

Re: Sorting Data - First sorted value - Pulling corresponding Text Field

Fri, 06 Jul 2012 09:06:56 GMT

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

Regards

Re: Sorting Data - First sorted value - Pulling corresponding Text Field

Fri, 06 Jul 2012 09:09:32 GMT

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...

Re: Sorting Data - First sorted value - Pulling corresponding Text Field

Fri, 06 Jul 2012 09:24:58 GMT

Here you are my script:

Hope it will help you

Re: Sorting Data - First sorted value - Pulling corresponding Text Field

Anonymous — Fri, 06 Jul 2012 10:38:16 GMT

hi,

the following will help u for sure...

PFA

Re: Sorting Data - First sorted value - Pulling corresponding Text Field

nagaiank — Sun, 08 Jul 2012 01:54:46 GMT

Assuming the data is loaded using the script

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
];

define the properties of the Summary table as below:

Dimension: Year

Expression 1 (for Team column) : FirstSortedValue(Team,Count)

Expression 2 (for Count column): FirstSortedValue(Count,Count)

With the above properties, the summary table will respond to the selections in the Data table fields.

Re: Sorting Data - First sorted value - Pulling corresponding Text Field

prasadmundewadi — Tue, 10 Jan 2017 21:06:00 GMT

I tried this script and somehow it is not working. Here is a screenshot. FirstOrderValue always return null for me.