Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

6 Replies
jonathan_dau
Contributor III
Contributor III

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

Not applicable
Author

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

jonathan_dau
Contributor III
Contributor III

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

Not applicable
Author

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

Not applicable
Author

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

jonathan_dau
Contributor III
Contributor III

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