topic Re: SHOW 1 ROW BY EXPRESSION AND GROUP in QlikView

SHOW 1 ROW BY EXPRESSION AND GROUP

inigoelv — Sat, 11 Jul 2015 21:35:34 GMT

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,

Re: SHOW 1 ROW BY EXPRESSION AND GROUP

sinanozdemir — Sat, 11 Jul 2015 21:54:07 GMT

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.

Re: SHOW 1 ROW BY EXPRESSION AND GROUP

sinanozdemir — Sat, 11 Jul 2015 22:06:29 GMT

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:

Re: SHOW 1 ROW BY EXPRESSION AND GROUP

sunny_talwar — Sun, 12 Jul 2015 00:01:31 GMT

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

Re: SHOW 1 ROW BY EXPRESSION AND GROUP

maxgro — Sun, 12 Jul 2015 04:49:56 GMT

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

Source:

LOAD

rowno() as id,

[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 id;

DROP Table Source;