topic Re: HELP!!!!!!!!!!!!!!!!!! in QlikView

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

Fri, 15 Feb 2013 07:25:07 GMT

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

Re: HELP!!!!!!!!!!!!!!!!!!

alexandros17 — Fri, 15 Feb 2013 08:00:52 GMT

Here is an example, hope it helps

Re: HELP!!!!!!!!!!!!!!!!!!

Fri, 15 Feb 2013 08:12:20 GMT

When I open the qvw file which you have sent me I just see a table with a value 0 in it?

Re: HELP!!!!!!!!!!!!!!!!!!

Fri, 15 Feb 2013 08:48:05 GMT

LOAD p_name,

count(p_name) as count,

//division,

// disig,

max(date(date))

FROM

(ooxml, embedded labels, table is Sheet1) Group by p_name;

Re: HELP!!!!!!!!!!!!!!!!!!

sujeetsingh — Fri, 15 Feb 2013 08:50:07 GMT

Balu it is just as you can add a derived field as level and then you can easily do the ranking.

Re: HELP!!!!!!!!!!!!!!!!!!

alexandros17 — Fri, 15 Feb 2013 08:50:19 GMT

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

Fri, 15 Feb 2013 08:57:55 GMT

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

alexandros17 — Fri, 15 Feb 2013 09:05:15 GMT

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

Fri, 15 Feb 2013 09:18:42 GMT

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

alexandros17 — Fri, 15 Feb 2013 09:41:33 GMT

I hope you can see the example, if not let me know, I've solved the problem

Hope it helps

Re: HELP!!!!!!!!!!!!!!!!!!

Fri, 15 Feb 2013 10:11:31 GMT

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

alexandros17 — Fri, 15 Feb 2013 10:15:23 GMT

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

Fri, 15 Feb 2013 10:28:14 GMT

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

alexandros17 — Fri, 15 Feb 2013 10:34:17 GMT

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

Fri, 15 Feb 2013 11:16:13 GMT

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

Re: HELP!!!!!!!!!!!!!!!!!!

alexandros17 — Fri, 15 Feb 2013 12:39:34 GMT

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:

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

Fri, 15 Feb 2013 12:54:41 GMT

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

alexandros17 — Fri, 15 Feb 2013 13:10:29 GMT

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

Fri, 15 Feb 2013 13:12:55 GMT

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

alexandros17 — Fri, 15 Feb 2013 14:20:50 GMT

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

topic Re: HELP!!!!!!!!!!!!!!!!!! in QlikView

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

Person Name

Division

Desigination

Ch﻿ange date

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Re: HELP!!!!!!!!!!!!!!!!!!

Change date