QlikView Sorting Table/Chart

[#1]

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

[manojkvrajan]

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, User1

    Worknet2, User3

    Worknet1, User3

    Worknet2, User3

    Worknet2, User3

    Worknet4, User2

    Worknet4, User2

    Worknet4, User2

    Worknet4, User3

    Worknet4, User4

];

WorknetUserTemp:

LOAD DISTINCT Worknet,

COUNT(UserName) AS Count

RESIDENT WorknetExtract

GROUP BY Worknet;

DROP TABLE WorknetExtract;

[#3]

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

[manojkvrajan]

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.

[#5]

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.

[#6]

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.