topic Re: Top x line into a straight table in QlikView

Top x line into a straight table

tunoi — Wed, 25 Nov 2020 16:16:04 GMT

Hi guys,

i need help with one problem.

i have the following input data:

Dep	Client	Status	IDP	Value
1	a1	Live	1	100
1	a1	Expired	2	150
1	a2	Live	3	99
1	a3	Live	4	101
1	a4	Live	5	102
1	a5	Live	1	103
1	a6	Live	2	104
1	a7	Live	3	105
1	a8	Live	4	106
1	a9	Live	5	107
2	a10	Live	6	108
2	a11	Live	7	109
2	a12	Live	8	110
2	a13	Live	9	111
2	a14	Live	7	112
1	a15	Expired	3	88

And i want to set up a straight table in which to display only first 10 lines from it sorting by Status(live first and the expired) and after that by value descending so in my case if i select dep 1 i will have the following expected output:

Client	Status	IDP	Sum(value)
a9	Live	5	107
a8	Live	4	106
a7	Live	3	105
a6	Live	2	104
a5	Live	1	103
a4	Live	5	102
a3	Live	4	101
a1	Live	1	100
a2	Live	3	99
a1	Expired	2	150

If i dont select anything i should have:

Client	Status	IDP	Sum(value)
a14	Live	7	112
a13	Live	9	111
a12	Live	8	110
a11	Live	7	109
a10	Live	6	108
a9	Live	5	107
a8	Live	4	106
a7	Live	3	105
a6	Live	2	104
a5	Live	1	103

till now I've tried with dimensional limit and/or Rank function but i didn't get the expected output. Anyone have any idea?

Re: Top x line into a straight table

kamal_sanguri — Thu, 15 Jun 2017 10:58:48 GMT

have a look..

Re: Top x line into a straight table

tunoi — Thu, 15 Jun 2017 11:06:05 GMT

Hi Kamal,

Your example don't work as expected when i don't have any department selected(it return 11 lines instead of 10 as expected and also value should be an expression in the straight table not a dimension.

Re: Top x line into a straight table

kamal_sanguri — Thu, 15 Jun 2017 11:09:38 GMT

Check this..

Re: Top x line into a straight table

tunoi — Thu, 15 Jun 2017 12:25:43 GMT

ty again but still it's not good because if i have a new client for the dep 1 in status Expired then i will have first 11 rows as output.

PS: i added a new line into entry data so this case will be visible.

Re: Top x line into a straight table

kamal_sanguri — Fri, 16 Jun 2017 06:14:59 GMT

I think, I have a working copy now... Check this out.

Re: Top x line into a straight table

kamal_sanguri — Fri, 16 Jun 2017 06:51:39 GMT

Nothing selected:

Selected 1 in Dep:

Selected 2 in Dep:

Re: Top x line into a straight table

tunoi — Fri, 16 Jun 2017 14:01:55 GMT

Thanks again for trying but still that wont work because that data are some mock up data. The actual dates are more complex and i have multiple lines for each combination of dimension. Also the dates have a temporal dimension, some selection will be done on that temporal dimension and some client will not have values in that period so sorting the dates in script and adding that rowno will be useless.

The solution if any exists have to be doable only in interface.

Regards,

Ionut

Re: Top x line into a straight table

antoniotiman — Fri, 16 Jun 2017 14:33:44 GMT

Hi Bostan,

see Attachment.

Regards,

Antonio