topic Re: QlikView Sorting Table/Chart in QlikView

QlikView Sorting Table/Chart

Thu, 23 Aug 2012 13:34:39 GMT

Hi guys,

I'm new in QlikView community and working with QllikView for almost a month and a half (ocasionaly).

I'm having a problem.

I want to find a way to have a visual graph or table representation as bellow:

- i have different worknets with a different number of users

- i want to sort these worknets by number of users meaning : how many worknets have 1 user, how many worknets have 2 users, etc, how many worknets have have 5-10 Users and so on ...

- i made a pivot table, it's simple to sort the worknets by number of users, i try to sum the worknets with the same number of users or with same above criteria, and i did not succed ... I want to have the posibility to select and show (also on a graph if it's possible, with bars ref 1 USER, 2 USER, 3 USER ... etc) the above criteria ...

Thanks a lot for helping us - the beginners

Re: QlikView Sorting Table/Chart

manojkvrajan — Thu, 23 Aug 2012 13:48:17 GMT

Crisitian, Welcome to QlikCommunity!!! Please find below the script meeting your requirements. Please replace the WorknetExtract inline table with your datasource. Attached is the sample file for your reference. I hope it helps.

WorknetExtract:

LOAD * INLINE [

Worknet, UserName

Worknet1, User1

Worknet2, User1

Worknet3, User1

Worknet4, User1

Worknet1, User1

Worknet2, User1

Worknet2, User3

Worknet1, User3

Worknet2, User3

Worknet4, User2

Worknet4, User3

Worknet4, User4

];

WorknetUserTemp:

LOAD DISTINCT Worknet,

COUNT(UserName) AS Count

RESIDENT WorknetExtract

GROUP BY Worknet;

DROP TABLE WorknetExtract;

Re: QlikView Sorting Table/Chart

Thu, 23 Aug 2012 14:02:24 GMT

Hi Manoj,

Thanks a lot for your answer !

I don't really think that is my desired solution. Maybe I didn't express myself more exactly ...

I have a lot's of Worknets with different number of users. My desired graph or table way is to see how many (as number) worknets have a specific number of users, for example: 3 Worknets have 1 User, 5 Worknets have 2 Users, 7 Worknets have 3 Users .... 14 Worknets have 5 - 10 Users, 11 Worknets have 10 - 20 Users and 34 Worknets have more than 20 Users ...

I hope it's more understandable now ...

Re: QlikView Sorting Table/Chart

manojkvrajan — Thu, 23 Aug 2012 14:40:34 GMT

Cristian, I can understand. However, attached is the qvw aligned with your requirements. Please send me a sample dataset if this doesn't help and I can see if I can provide you a ready to use solution.

Re: QlikView Sorting Table/Chart

Fri, 24 Aug 2012 07:21:14 GMT

Hi Manoj,

Due confidentiality reason I am not allowed to post any of my data ...

I have hundreds of worknets on my data .. and those should be categorised.

I try to use your ideea but I guess the database structure of mine is kind of complex and not to simple to insert in my script ..

I keep on trying to find a workaround for my goal.

Thanks a lot.

Re: QlikView Sorting Table/Chart

Tue, 28 Aug 2012 12:10:31 GMT

Hi guys,

Here is the elegant solution to my problem: Aggr function !

To calculate the number of users,I used the function sum(#Users), and the identifier for worknets is Worknet_ID for example.

The dimension will be Aggr(sum(#Users), Worknet_ID). This will give a number as a result, which is the number of users for a worknet.

As the expression I used count(Worknet_Id) or sum(#Worknets) or something like that and use also DISTINCT.

For the classes: input manually as belllow:

If( aggr(sum(#Users), WorknetId)<5, aggr(sum(#Users), WorknetId),

If(aggr(sum(#Users), WorknetId)<10, ‘5-10’,

If(aggr(sum(#Users), WorknetId)<25, ‘10-25’)))

Succes on your QlikView Projects.