Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
sasi
Contributor II
Contributor II

Top 4 in each dimension Value

Hi All,

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

sasi_0-1613992202192.png

Regards,

Sasi

1 Solution

Accepted Solutions
salonicdk28
Creator II
Creator II

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;

View solution in original post

9 Replies
Patricia_Silva
Specialist II
Specialist II

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.

Help users find answers! Don't forget to mark a correct resolution 🙂
agigliotti
Partner - Champion
Partner - Champion

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
Contributor II
Contributor II
Author

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

 

sasi_1-1614002539490.png

Regards,

Sasi

agigliotti
Partner - Champion
Partner - Champion

ok then you can use:

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

Patricia_Silva
Specialist II
Specialist II

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.

Help users find answers! Don't forget to mark a correct resolution 🙂
NitinK7
Specialist
Specialist

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;

NitinK7_0-1614003429619.png

 

salonicdk28
Creator II
Creator II

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
Contributor II
Contributor II
Author

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

sasi_0-1614003787371.png

 

Regards,

Sasi.

 

manoranjan_d
Specialist
Specialist

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;