HELP!!!!!!!!!!!!!!!!!!

[#1]

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

[#2]

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?

[alexandros17]

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 ...

[#4]

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

[alexandros17]

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!

[#6]

Yes please help me and see if we can display the info and probably in a chart?

[alexandros17]

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

[#8]

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.

[alexandros17]

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.

[#10]

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?

[alexandros17]

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