SHOW 1 ROW BY EXPRESSION AND GROUP

inigoelv · ‎2015-07-11

Hi everybody,

I need the help for showing the first row by group of Header1:.

Per example:

Header 1	Header 2	Header 3	Total
A	1	Red	200
A	5	Green	400
B	2	Red	300
B	8	Blue	350
B	7	White	800
C	3	Black	123
C	9	Green	234
C	7	White	456

The result I expected, by expression if it is possible, is this:

Header 1	Header 2	Header 3	Total
A	1	Red	200
B	2	Red	300
C	3	Black	123

Any idea?

Many thanks,

sinanozdemir · ‎2015-07-11

Hi,

You can achieve this in a straight table:

Get Header 1 as your dimension and Min(Header 2) and Min(Total), and for Header 3 I used the firstsortedvalue:

Hope this helps.

sinanozdemir · ‎2015-07-11

If you wanted to create it in the load script, the below is a way of doing it:

And the data model looks like this:

sunny_talwar · ‎2015-07-11

Just to add another option is to create a flag in the script:

Table:

LOAD [Header 1],

[Header 2],

[Header 3],

Total

FROM

[https://community.qlik.com/thread/172203]

(html, codepage is 1252, embedded labels, table is @1);

Join(Table)

LOAD [Header 1],

Min([Header 2]) as [Header 2],

1 as Flag

Resident Table

Group By [Header 1];

Table Box Object below will show a 1 next to the fields you want to show in your chart object:

and you can use this flag in your chart to only show rows where flag = 1

Attaching the sample qvw for reference.

Best,

Sunny

maxgro · ‎2015-07-12

1) if you want the min Header 2 by Header 1

Source:

LOAD

[Header 1],

[Header 2],

[Header 3],

Total

FROM

[https://community.qlik.com/thread/172203]

(html, codepage is 1252, embedded labels, table is @1);

Final:

NoConcatenate LOAD *

Resident Source

Where [Header 1] <> Peek([Header 1])

order by [Header 1], [Header 2];

DROP Table Source;

2) if you want the first row in the table by Header 1 (with your data the result is the same as 1) )