topic Re: Top 4 in each dimension Value in QlikView

Top 4 in each dimension Value

sasi — Mon, 22 Feb 2021 11:15:37 GMT

Hi All,

I am using Excel as source. My requirement is to pick only top 4 Values in ID Field .Help me out?

Regards,

Sasi

Re: Top 4 in each dimension Value

Patricia_Silva — Mon, 22 Feb 2021 12:47:40 GMT

Hello Sasi, do you mean in script level? If so, then it would be like this:

Test:
FIRST 5 LOAD
id,
Sale
FROM [lib://DataFiles/Testopvalues.xlsx]
(ooxml, embedded labels, table is testTop);

If you mean you want to keep all the data you can also create a resident table where <5 and then use this table instead 😊

Hope this could help you.

Re: Top 4 in each dimension Value

agigliotti — Mon, 22 Feb 2021 13:57:34 GMT

Hi @sasi ,

In UI you can use the below expression:

=sum( {< Id = {"=rank(sum(Sale))<5"} >} Sale )

and Id as dimension.

I hope it can helps.

Best Regards

Re: Top 4 in each dimension Value

sasi — Mon, 22 Feb 2021 14:04:33 GMT

Team Thanks for Inputs, but my requirement is need to load colored values (i.e a(4 to 7) and b(14 to 17))

Regards,

Sasi

Re: Top 4 in each dimension Value

agigliotti — Mon, 22 Feb 2021 14:08:30 GMT

ok then you can use:

=sum( {< Id = {"=rank(sum(Sale))<5"} >} TOTAL <Id> Sale )

Re: Top 4 in each dimension Value

Patricia_Silva — Mon, 22 Feb 2021 14:09:57 GMT

Hello @sasi unfortunately I don't think it is possible to achieve so, as Qlik Sense cannot recognize if a cell is colored or not, it just will see data no matter what the background color is. So in this specific case you would need to specify which rank you want to load (from 4 to 7 and then from 14 to 17). Another idea that comes to my mind, maybe you can create an extra column on your data source (I guess it is an excel file) to check if the cell is colored, and if so, then write any character, so then you can specify it in the Qlik Script editor.

Re: Top 4 in each dimension Value

NitinK7 — Mon, 22 Feb 2021 14:18:26 GMT

Hi,

Please try below script

A:
Load * inline [
id,sale
a,1
a,2
a,3
a,4
a,5
a,6
a,7
b,8
b,9
b,10
b,11
b,12
b,13
b,14
b,16
b,17
];

NoConcatenate

B:
load
*,
AutoNumber(sale,id)
Resident A Where
AutoNumber(sale,id)<=4 Order by id,sale desc;
DROP Table A;

Re: Top 4 in each dimension Value

salonicdk28 — Mon, 22 Feb 2021 14:24:00 GMT

Temp:
Load * Inline [
ID, Sales
a,1
a,2
a,3
a,4
a,5
a,6
a,7
b,8
b,9
b,10
b,11
b,12
b,13
b,14
b,15
b,16
];

Table:
LOAD *

WHERE Rank <= 4;

LOAD ID, Sales, Autonumber(recno(), ID) as Rank

RESIDENT Temp

ORDER BY ID, Sales desc;
DROP Table Temp;

Re: Top 4 in each dimension Value

sasi — Mon, 22 Feb 2021 14:28:36 GMT

Thank you for your quick reply Patricia!!

a) For ID Field 'a'-(4,5,6,7) is highest values and for value 'B'-(14,15,16,17) is highest .So, My requirement is to Load only these values

Regards,

Sasi.

Re: Top 4 in each dimension Value

manoranjan_d — Mon, 22 Feb 2021 20:50:02 GMT

you can also try in this way also

T1:
Load * Inline [
ID, Sales
a,1
a,2
a,3
a,4
a,5
a,6
a,7
b,8
b,9
b,10
b,11
b,12
b,13
b,14
b,15
b,16
];

Table:
LOAD ID, Sales, Autonumber(Sales,ID)

RESIDENT T1
where Autonumber(Sales,ID) <=4
ORDER BY ID, Sales desc;
DROP Table T1;