Top 4 in each dimension Value

[sasi]

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

[Patricia_Silva]

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.

[agigliotti]

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

[sasi]

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

[agigliotti]

ok then you can use:

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

[Patricia_Silva]

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.

[NitinK7]

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;

[salonicdk28 · Accepted solution]

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;

[sasi]

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.

[manoranjan_d]

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;