Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

inigoelv
Contributor

SHOW 1 ROW BY EXPRESSION AND GROUP

Hi everybody,

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

Per example:

Header 1Header 2Header 3Total
A1Red200
A5Green400
B2Red300
B8Blue350
B7White800
C3Black123
C9Green234
C7White456

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

Header 1Header 2Header 3Total
A1Red200
B2Red300
C3Black123

Any idea?

Many thanks,

4 Replies
sinanozdemir
Valued Contributor III

Re: SHOW 1 ROW BY EXPRESSION AND GROUP

Hi,

You can achieve this in a straight table:

Capture.PNG

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

Capture2.PNG

Hope this helps.

sinanozdemir
Valued Contributor III

Re: SHOW 1 ROW BY EXPRESSION AND GROUP

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

Capture.PNG

And the data model looks like this:

Capture2.PNG

Re: SHOW 1 ROW BY EXPRESSION AND GROUP

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:

Capture.PNG

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

Capture.PNG

Attaching the sample qvw for reference.

Best,

Sunny

MVP
MVP

Re: SHOW 1 ROW BY EXPRESSION AND GROUP

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;

Community Browser