5 Replies Latest reply: Aug 28, 2012 8:10 AM by cristian_cazan RSS

    QlikView Sorting Table/Chart

      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
          Manoj Kumar Varatharajan

          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;

            • Re: QlikView Sorting Table/Chart

              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

              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.