Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ 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
Contributor III
Contributor III

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

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
Partner

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
Partner

ok then you can use:

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

Patricia_Silva
Support
Support

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
Contributor III
Contributor III

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

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;