6 Replies Latest reply: May 16, 2013 8:09 AM by Jonathan Dau RSS

    How chart (or load?) data with multiple status per item?

      Hi,

       

      As a newcomer to qlikview I now face my first non-schoolbook example when trying to load/chart some real world data that is stored in the below format

       

      ID
      Date
      Status
      ID12013-01-011
      ID12013-01-022
      ID22013-01-011
      ID22013-01-023
      ID22013-01-034
      ID22013-01-045

       

      The status in this case indicates just a log of flow of actions (e.g. start=1, initiated=2, analysed=3...). Say now I want to display a pie chart showing the relative distribution of these items. End result in this case is 50% (ID1) is in status=2, 50% (ID2) is in status=5 (i.e. I cannot count the status=1 for ID1).

       

      If possible it would be helpful for me to understand how to do this both by looking at status alone (max status value per IDx) and how to do it by looking at the date field (as max status may not always be applicable if status would decrease by some action, I should instead look at the latest change date per IDx but I still want to plot the Status relations).

       

      /Mattias

        • Re: How chart (or load?) data with multiple status per item?

          Hi,

           

          I think you can easily solve your problem by putting in Dimension of your graph Status, and in Expression a Count(ID).

          You only have to check the box Relative at the right side of the Expression.

           

          You can have a look at my quick test app joined to this post.

           

          Regards

           

          Jonathan

            • Re: How chart (or load?) data with multiple status per item?

              Hi Dau!

               

              Thanks for your reply but, no, that is not really what I wanted. You see the status 1,3,4,5 is all for the item with ID=ID2. So what I want really is to display the LATEST status for each item (i.e. max status / ID or status with the max date / ID).

               

              I.s. the pie chart would only have two areas in this case (50% each), one being status = 2, one being status = 5.

               

              /Mattias

                • Re: How chart (or load?) data with multiple status per item?

                  Ok I see, it was not so simple after all.

                   

                  I did something real quick but I am not sure I fully understood what you want.

                   

                  Hope that helps anyway.

                   

                  Jonathan

                    • Re: How chart (or load?) data with multiple status per item?

                      Hi Dau & thanks again!

                       

                      I think you may have misunderstood. In your example you work on the ID and date columns for selections/filtering but that is not really possible as I don't know the ID set beforehand.

                       

                      So what I need is one of:

                       

                      1) A solution that only works on the Status field (i.e. somehow counts the number if distinct IDs but only where Status is the max for each count).

                       

                      2) A solution similar to 1) but where the not max of status is used for filtering but instead finding the status where with the max Date.

                       

                      Hope this clarifies? I.e. there couldn't be any "input parameters" to the expression.

                       

                      Just to clarify even more, I could live with managing this during the loading as well (if e.g. adding new tables / columns could help for this scenario).

                       

                      /Mattias

                        • Re: How chart (or load?) data with multiple status per item?

                          Hi Mattias,

                           

                          Indeed in my solution the ID is not dynamic which is a bad limitation of my formulas.

                           

                          I did tried :

                          - aggr (max(Status),ID)  this give the right status but it's not the right one

                          - aggr (max(date(Date,'YYYY-MM-DD')),ID)  this gives the right date but I guess this is not useful like that.

                           

                          I think your solution would be to mix both formulas so that you can find for the Max(Date) for each ID the associated status.

                           

                          I didn't have time to find the solution myself.

                           

                          Sorry for that and hope you'll find the way to solve your issues

                           

                          Jonathan

                  • Re: How chart (or load?) data with multiple status per item?

                    I did a bit of a test working in the load script but my experience fails here too but maybe someone could assist here?

                     

                    Test:

                    LOAD * Inline [TestID, TestDate, TestStatus

                     

                     

                    ID1, 2013-01-01, 1

                    ID1, 2013-01-02, 2

                    ID2, 2013-01-01, 1

                    ID2, 2013-01-02, 3

                    ID2, 2013-01-03, 4

                    ID2, 2013-01-04, 5

                    ];

                     

                     

                    TestTmp:

                    LOAD TestID as TID1, TestStatus as TS1

                    Resident Test

                    Order by TestID, TestStatus DESC;

                     

                     

                    TestStatus:

                    LOAD

                              TID1 as TheID,

                              if(TS1 < previous(TS1), Previous(TS1), TS1) as StatusText

                    Resident TestTmp;

                     

                    My problem here is that using comparisons with previous only work 1 level back... So can I either check more  records back (fixed number or in some way for all status' based on the same ID?)

                     

                    /Mattias