Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Im really new to qlikView and I need to design a very complex chart and really need help,
Lets assume I have a table as below in excel file,
Person Name | Division | Desigination | Change date |
---|---|---|---|
Person A | IT | Engineer | 1/1/2013 |
Person B | IT | Enginer | 1/1/2013 |
Person A | IT | Manager | 2/2/2013 |
Person B | IT | Asst Manager | 3/3/2013 |
So I want a chart in qlikview which would tell me how many Person from each division has got promotion? So the Chart should display as In IT Division 2 People have got promotion
Im not able to open the example file is it possible to paste the code and also help me on if it is possible to display in a chart?
SET
ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;-€ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';
SET DayNames='lun;mar;mer;gio;ven;sab;dom';
MainTable:
load * Inline
[Person Name ,Division ,Desigination ,Change date
Person A,IT,Engineer,1/1/2013
Person B,IT,Engineer,1/1/2013
Person A,IT,Manager,2/2/2013
Person B,IT,Asst Manager,3/3/2013
];
left Join
load * Inline
[
Desigination, level
Engineer,1
Asst Manager, 2
Manager, 3
];
left join
LOAD
Division,
[Person Name],
max(level) as max,
min(level) as min
Resident MainTable
Group by Division,[Person Name];
left Join
load * Inline
[
From_Desigination, min
Engineer,1
Asst Manager, 2
Manager, 3
];
left Join
load * Inline
[
To_Desigination, max
Engineer,1
Asst Manager, 2
Manager, 3
];
I've designed a table chart
Dimension:
Division
person name
from_designation
to_designation
change_date
Expression:
aggr
(sum(if(min<max,1,0)),Division,[Person Name])
If you need help let me know ...
Thanks for this the only problem I see now is If the Person A Changes from Enginer to Asst manager and then To manager then I would expect to display all the changes but that is not happening. Also can't we display this information in some chart instead of a straight table? Please suggest
I've computed only first and last change, if you want a sort of history things changes ... I'll try to display info but tell me how display data in the row!
Yes please help me and see if we can display the info and probably in a chart?
If you want history of changes, try the following code:
MainTable:
NoConcatenate
load * Inline
[Person Name ,Division ,Desigination ,Change date
Person A,IT,Engineer,1/1/2013
Person B,IT,Engineer,1/1/2013
Person A,IT,Manager,2/2/2013
Person B,IT,Asst Manager,3/3/2013
Person B,IT,Manager,1/2/2013
];
Left Join
levels:
load * Inline
[
Desigination, level
Engineer,1
Asst Manager, 2
Manager, 3
];
then design a table with dimension
Division
person name
level
designation
Expression:
1
and sort by
Division
person name
level
If you use the following expression (for example in a bar chart) you can count the number of changes
aggr
(distinct count(DISTINCT level),Division, [Person Name])
Hope it helps
Alexandros
I pasted the script as below in the qlikview and then I reloaded the data and created barchart with Dimension as Division,Designation and Person Name and the expr as
aggr
(distinct count(DISTINCT level),Division, [Person Name]) but I see the Bar chart showing Just Person A and Person B info and it is showing only for engineer but not the way I wanted. Like in the chart I want to know in which Divishion which employee is chnaged from which designation to which.
I've done 2 chart, a table and a bar graph, in the table I use a dummy expression (e.g. sum(1)), in the bar chart I use the second expression I gave you.
In the table the information is shown by rows.
Can you please attach the QVW file which you have designed?
Also can you suggest which is the best way to learn scripts in qlikview can you refer some books?
This is the final solution, you can obviously generalize it but it works ...
SET
ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;-€ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';
SET DayNames='lun;mar;mer;gio;ven;sab;dom';
levels_tmp:
load * Inline
[
Desigination, level
Engineer,1
Asst Manager, 2
Manager, 3
];
MainTable:
NoConcatenate
load * Inline
[Person Name ,Division ,Desigination ,Change date
Person A,IT,Engineer,1/1/2013
Person B,IT,Engineer,1/1/2013
Person A,IT,Manager,2/2/2013
Person B,IT,Asst Manager,3/3/2013
Person B,IT,Manager,1/2/2013
];
Left Join
levels:
load * Inline
[
Desigination, level
Engineer,1
Asst Manager, 2
Manager, 3
];
MT:
NoConcatenate
LOAD * Resident MainTable;
FOR i = 1 to 3
// LOAD * Resident MainTable;
Left Join
LOAD level,Desigination as des_descr$(i) Resident levels_tmp where level=$(i);
NEXT
DROP Table MainTable;
DROP Table levels_tmp;
All:
NoConcatenate
LOAD
Division, [Person Name], MaxString(des_descr1) as des_descr1, MaxString(des_descr2) as des_descr2, MaxString(des_descr3) as des_descr3
Resident MT
Group by Division, [Person Name];
DROP Table MT;
THE TABLE CHART DIMENSIONS ARE:
Division
Person Name
des_descr1
des_descr2
des_descr3
Use 1 as Expression (you can even hide the column