topic Re: Sequential number instead of Dimension values in QlikView

Sequential number instead of Dimension values

divya_anand — Wed, 26 Jul 2017 10:43:02 GMT

Hi,

I am working on a line chart which has ID on X-axis and "no of days open" on Y-axis.

I am trying to name these IDs in numbers like this-

Any idea as to how to name these IDs in numbers?

Re: Sequential number instead of Dimension values

ahaahaaha — Wed, 26 Jul 2017 10:53:09 GMT

Hi,

May be add a match table like the one below and use a new dimension [NewField]?

LOAD*Inline

[Field, NewField

10$, 1

44$, 2

$5, 3

$2, 4

$30, 5

$12, 6

];

Re: Sequential number instead of Dimension values

tresB — Wed, 26 Jul 2017 10:57:27 GMT

May be using autonumber(), like:

Load

AutoNumber("no of days open") as Sequence

"no of days open",

...

And then use Sequence as dimension.

Re: Sequential number instead of Dimension values

Kushal_Chawda — Wed, 26 Jul 2017 11:05:01 GMT

another option is to use Rank function to convert your dimension in number

=aggr(rank(ID),ID)

Re: Sequential number instead of Dimension values

divya_anand — Wed, 26 Jul 2017 12:01:06 GMT

Hi Andrey,

Thank you for the response.

This is just an example showing 6 values, but in my database contains a huge list - Not practical this way.

Re: Sequential number instead of Dimension values

rwunderlich — Wed, 26 Jul 2017 16:56:07 GMT

FieldIndex('ID', ID)

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Sequential number instead of Dimension values

divya_anand — Sun, 30 Jul 2017 14:16:07 GMT

Hi Rob,

Thank you for the response.

FieldIndex provides the load position of the value right?

I am looking for the ordering in the chart itself, whether the ID is at position 100 or 1000 in the load order doesn't matter. Only the position of that ID within the chart is to be displayed ( 1,2,3,4..... from left to right). Is this possible?

Re: Sequential number instead of Dimension values

divya_anand — Sun, 30 Jul 2017 14:18:05 GMT

Hi Kushal,

Thank you for the response.

This only converts ID into a number, but doesn't display numbers starting from 1. Any other thought?

Re: Sequential number instead of Dimension values

divya_anand — Sun, 30 Jul 2017 14:26:29 GMT

Hi Tresesco,

I am trying to avoid working with datamodel because "no of days open" is not just a field, it is an expression with several conditions on fields in more than 1 table. If I don't find a solution which can be implemented in the layout itself, I'll give this a try and will keep you posted.

Thank you.

Re: Sequential number instead of Dimension values

MarcoWedel — Sun, 30 Jul 2017 15:38:58 GMT

please provide some sample data as well as the expression you are referring to.

thanks

regards

Marco

Re: Sequential number instead of Dimension values

effinty2112 — Sun, 30 Jul 2017 18:17:08 GMT

Hi Divya,

Maybe:

Aggr(Dual(ID,Rank(Expression)),ID)

Good luck

Andrew

Re: Sequential number instead of Dimension values

Nagaraju_KCS — Sun, 30 Jul 2017 19:13:47 GMT

Try this,

Make one composite key Script level...

RowNo() &'-'&YourFields as NewField

and in your chart Dimension

SubField(NewField,'-', 1)

Only take RowNo() as Key in script

and in your chart Dimension

SubField(Key&'-'&YourDimension,'-', 1)

Good luck

Re: Sequential number instead of Dimension values

rwunderlich — Mon, 31 Jul 2017 00:42:06 GMT

How are the IDs currently sorted on the Xaxis?

-Rob

Re: Sequential number instead of Dimension values

divya_anand — Mon, 31 Jul 2017 04:20:34 GMT

Rob,

The IDs are supposed to be sorted in descending order of "No. of days open". So all the IDs which have higher value for "No. of days open" will appear on the left and the line will gradually drop because all the IDs with lower value of "No. of days open" will appear on the right side of the chart.

Irrespective of which ID is on X-axis, it has to be numbered 1,2,3.... from left to right. This will help users get a count of IDs instead of the IDs itself.

Thank you.

Re: Sequential number instead of Dimension values

tresB — Mon, 31 Jul 2017 05:35:46 GMT

If you are using qv12.x or qlik sense, you could probably use sortable aggr() to achieve this.

Re: Sequential number instead of Dimension values

rwunderlich — Mon, 31 Jul 2017 16:38:37 GMT

I would try Andrew's suggestion below.

-Rob

Re: Sequential number instead of Dimension values

divya_anand — Wed, 02 Aug 2017 08:51:39 GMT

Hi Andrew,

I tried this on sample data, it still shows the ID itself on X-axis. I want it to be named 1,2,3,4..... on the chart.

PFA the sample qvw.

Could you please check & let me know if any change is to be made?

Thank you.

Re: Sequential number instead of Dimension values

divya_anand — Wed, 02 Aug 2017 08:53:32 GMT

Hi Marco,

I've attached a sample QVW in a reply to Andrew below.

Please let me know if you need more information.

Thank you.

Re: Sequential number instead of Dimension values

tresB — Wed, 02 Aug 2017 08:57:11 GMT

This?

Re: Sequential number instead of Dimension values

divya_anand — Wed, 02 Aug 2017 09:01:23 GMT

Hi Tresesco,

Thank you for the quick response.

I see that you have considered RowNo() to number them based on load order, right?

But can we make this chart to show values in descending order like this-

Yet name them 1,2,3,4... from left to right?

Is this possible?