-
Re: HELP!!!!!!!!!!!!!!!!!!
Alessandro Saccone Feb 15, 2013 3:00 AM (in response to balu_prg)Here is an example, hope it helps
-
aaaa.qvw 150.8 K
-
-
Re: HELP!!!!!!!!!!!!!!!!!!
balu_prg Feb 15, 2013 3:12 AM (in response to balu_prg)When I open the qvw file which you have sent me I just see a table with a value 0 in it?
-
Re: HELP!!!!!!!!!!!!!!!!!!
Vishwaranjan Kumar Feb 15, 2013 4:06 AM (in response to balu_prg)hi
a:
LOAD p_name,
count(p_name) as count,
//division,
// disig,
max(date(date))
FROM
[D:\SAP\QLIKVIEW-QVW-QVD FILE_NEW_2013\TEST.xlsx]
(ooxml, embedded labels, table is Sheet1) Group by p_name;
-
Re: HELP!!!!!!!!!!!!!!!!!!
sujeet Singh Feb 15, 2013 3:50 AM (in response to balu_prg)Balu it is just as you can add a derived field as level and then you can easily do the ranking.
-
Re: HELP!!!!!!!!!!!!!!!!!!
Alessandro Saccone Feb 15, 2013 3:50 AM (in response to balu_prg)this is the expression:
sum
(if(aggr(min(level),Division, [Person Name])<aggr(max(level),Division, [Person Name]),1,0))
the chart has two dimensions:
division and person name
the script is:
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
];
However try to load data again in the file I sent to you
-
Re: HELP!!!!!!!!!!!!!!!!!!
balu_prg Feb 15, 2013 3:57 AM (in response to Alessandro Saccone )I pasted the one which you gave me in QVW but What I see in the Bar chat is Person A and Person B and Division shows as IT but I also wanted to know From what Designation to what designation each person has changed. Also is it possible for you to explain me the expression?
-
Re: HELP!!!!!!!!!!!!!!!!!!
Alessandro Saccone Feb 15, 2013 4:05 AM (in response to balu_prg)I've designed a chart flat table ...
So you would like a bar chart with person name and division, but how would you display the information of changing designation? Isn't better to show information with a table? Let me know
-
Re: HELP!!!!!!!!!!!!!!!!!!
balu_prg Feb 15, 2013 4:18 AM (in response to balu_prg)I understand what you are saying but somehow I want to show this in a chart Im fine to show the chnage of designation in any chart not just bar so please help me
-
-
-
Re: HELP!!!!!!!!!!!!!!!!!!
Alessandro Saccone Feb 15, 2013 4:41 AM (in response to balu_prg)I hope you can see the example, if not let me know, I've solved the problem
Hope it helps
-
Example.qvw 156.0 K
-
Re: HELP!!!!!!!!!!!!!!!!!!
balu_prg Feb 15, 2013 5:11 AM (in response to Alessandro Saccone )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?
-
Re: HELP!!!!!!!!!!!!!!!!!!
Alessandro Saccone Feb 15, 2013 5:15 AM (in response to balu_prg)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 ...
-
Re: HELP!!!!!!!!!!!!!!!!!!
balu_prg Feb 15, 2013 5:28 AM (in response to Alessandro Saccone )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
-
Re: HELP!!!!!!!!!!!!!!!!!!
Alessandro Saccone Feb 15, 2013 5:36 AM (in response to balu_prg)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!
-
Re: HELP!!!!!!!!!!!!!!!!!!
balu_prg Feb 15, 2013 6:16 AM (in response to balu_prg)Yes please help me and see if we can display the info and probably in a chart?
-
Re: HELP!!!!!!!!!!!!!!!!!!
Alessandro Saccone Feb 15, 2013 7:39 AM (in response to balu_prg)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
-
Re: HELP!!!!!!!!!!!!!!!!!!
balu_prg Feb 15, 2013 7:54 AM (in response to Alessandro Saccone )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.
-
Re: HELP!!!!!!!!!!!!!!!!!!
Alessandro Saccone Feb 15, 2013 8:10 AM (in response to balu_prg)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.
-
Re: HELP!!!!!!!!!!!!!!!!!!
balu_prg Feb 15, 2013 8:12 AM (in response to Alessandro Saccone )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?
-
Re: HELP!!!!!!!!!!!!!!!!!!
Alessandro Saccone Feb 15, 2013 9:20 AM (in response to balu_prg)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
-
Re: HELP!!!!!!!!!!!!!!!!!!
balu_prg Feb 18, 2013 3:28 AM (in response to Alessandro Saccone )If you could post me the qvw file it will be very helpful
-
Re: HELP!!!!!!!!!!!!!!!!!!
Alessandro Saccone Feb 18, 2013 3:41 AM (in response to balu_prg)I have no more the example I've made, just copy the code I gave you and follow instructions ...
-
Re: HELP!!!!!!!!!!!!!!!!!!
balu_prg Feb 18, 2013 4:04 AM (in response to Alessandro Saccone )I actually did not ask for any more example I just asked you to attach the qvw field which you have written the above script so i can validate my o/p.
-
-
-
-
-
-
-
-
-
-
-
-
-
Re: HELP!!!!!!!!!!!!!!!!!!
Alessandro Saccone Feb 18, 2013 4:03 AM (in response to balu_prg)Here is the document
-
Example.qvw 152.2 K
-
Re: HELP!!!!!!!!!!!!!!!!!!
Alessandro Saccone Feb 18, 2013 4:08 AM (in response to Alessandro Saccone )That's it ...
Hope it could really help!
-
Re: HELP!!!!!!!!!!!!!!!!!!
balu_prg Feb 18, 2013 4:40 AM (in response to Alessandro Saccone )Thanks a lot for your reply. I wont need your view in one other discussion which I have posted Im really not sure if the one is possible using qlikview can you please have a look and respond? The posting title is
Charts for comparing data
-
Re: HELP!!!!!!!!!!!!!!!!!!
balu_prg Feb 19, 2013 12:45 AM (in response to balu_prg)Hi Alex,
Have you got a min to look at my post on "Charts for Comparing data"?
-
-
-