Sorry I am quite new to qlikview so can I ask - what does the first line mean?
LOAD * WHERE len(PositionChange);
I know it is finding the length of position change? Why do we need this?
Also, if I don't want to load the result table in the script but I simply want to prepare the results table in the dashboard, is there another way of doing this? Hope this makes sense.
I used the WHERE Len(PositionChange) to filter the records with a position change. You can remove the preceding load, but then you will get records with all Names and positions.
In the frontend, you can create a straight table chart with two dimensions, Name and Date.
This should already return the requested table. If not, please check that 'suppress zero values' is enabled on presentation tab of chart properties and that the sort order is Name first, then Date numeric ascending.
Try this in the script. If you're very new to QV this might be a bit difficult but it uses a preceding load and some functions you might not yet be familiar with. The table created by the inline load doesn't make it into the final data model instead it feeds records up to the load statement above, the one that creates the table I've called Changes.
So we group by Name because we only want one record per person.
The concat statement is a summary on the Position field that performs a string concatenation on the values in the Position field of the inline table. '=>' is the string that separates the values and Date is the sort weight. This last parameter ensures that the Position field values are concatenated in the correct order, from earliest date to most recent.
Max(Date) gives the date of the most recent appointment.
Changes:
Load
Name,
Concat(Position,'=>',Date) as Positions,
Max(Date) as [Last Appointment]
Group by Name;
LOAD * INLINE [
Name, Position, Date
John, Secretary, 12/1/2014
Peter, Assistant Manager, 6/12/2014
John , Resource Coordinator, 20/12/2014
Mary , Manager, 1/1/2015
Zyed , Manager, 1/2/2015
Zyed , General Manager , 13/11/2015
John , Resource Manager, 14/11/2015
]
The script returns this table:
Name
Positions
Last Appointment
John
Secretary=>Resource Coordinator=>Resource Manager
14/11/2015
Mary
Manager
1/1/2015
Peter
Assistant Manager
6/12/2014
Zyed
Manager=>General Manager
13/11/2015
You can do lots with the Concat statement here
You can combine the Position and Date field with a Concat statement such as :
Concat(Position & ' (' & Date & ') ','=>',Date) as [Appointment Dates]
