topic Re: Creating a table showing changes in another table in QlikView

Creating a table showing changes in another table

Sat, 14 Nov 2015 09:01:58 GMT

I have a table showing a list of employees in a company, the position that they are in and the date they started their positions:

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

I want to prepare a table that shows the following:

Name Position Change Date Change

John Secretary -> Resource Coordinator 20/12/2014

John Resource Coordinator -> Resource Manager 14/11/2015

Zyed Manager -> General Manager 13/11/2015

Is this possible in qlikview?

I am very new to qlikview so any help would be greatly appreciated!!!

Re: Creating a table showing changes in another table

swuehl — Sat, 14 Nov 2015 11:20:08 GMT

Maybe like this:

SET DATEFORMAT = 'D/M/YYYY';

INPUT:

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

];

RESULT:

LOAD * WHERE len(PositionChange);

LOAD Name,

If(Name= Previous(Name),Previous(Position) & ' -> ' & Position ) as PositionChange,

Date as DateOfChange

RESIDENT INPUT ORDER BY Name, Date ASC;

Re: Creating a table showing changes in another table

Sat, 14 Nov 2015 12:52:04 GMT

Thank you so much!!!

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.

Re: Creating a table showing changes in another table

swuehl — Sat, 14 Nov 2015 17:43:27 GMT

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.

Then use one expression:

=If(Len(Above(Position)), Above(Position) &'->' &Position,0)

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.

Re: Creating a table showing changes in another table

MarcoWedel — Sun, 15 Nov 2015 22:08:39 GMT

Hi,

one solution could be also:

hope this helps

regards

Marco

Re: Creating a table showing changes in another table

effinty2112 — Sun, 15 Nov 2015 22:46:26 GMT

Hi,

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]

and then we get a two field table like this:

Name	Appointment Dates
John	Secretary (12/1/2014) =>Resource Coordinator (20/12/2014) =>Resource Manager (14/11/2015)
Mary	Manager (1/1/2015)
Peter	Assistant Manager (6/12/2014)
Zyed	Manager (1/2/2015) =>General Manager (13/11/2015)

Hope this helps!

Andrew