topic Re: Grouping Experience in QlikView

Grouping Experience

Thu, 12 Sep 2013 11:22:10 GMT

Hi Friends,

I have a field of experience from 1 to 20 yrs. I want to group the experience as 1-5 yrs, 6-10 yrs and 10+ yrs showing the number of emps laying in it.

thanx

Re: Grouping Experience

swuehl — Thu, 12 Sep 2013 11:25:14 GMT

LOAD

Experience,

if(Experience <6, dual('1-5 yrs',5),

if(Experience < 11, dual('6-10 yrs', 10), dual('10+ yrs', 20) ) ) as ExpGroup

FROM ...;

Then use ExpGroup as dimension and count(ExpGroup) as expression.

Re: Grouping Experience

dmohanty — Thu, 12 Sep 2013 11:51:23 GMT

Hi AB,

You can do this in the Expression of the list Box as well. Add the field Years from Experience table in a List Box, go to expression and put the below script:

if(Years <6, DUAL('1-5 Years',5),

if(Years < 11, DUAL('6-10 Years', 10),

DUAL('10+ Years', 20) ) )

Re: Grouping Experience

Anonymous — Thu, 12 Sep 2013 11:58:28 GMT

Try calculated dimention:

if ( Year >= 10, '10+', Class( Year, 5 ) )

Remember to sort by Year

Re: Grouping Experience

Thu, 12 Sep 2013 12:26:53 GMT

Hi Swuehl,

I was trying yr code but the EmpGroup is not creating. I am attaching the snap shot of the code. Plz chk it.

Re: Grouping Experience

Thu, 12 Sep 2013 12:33:46 GMT

I have fields Emp name, Exp, rating and few more. I am making a pie chart, which drill down from Rating to Experience. In Experience it shows the number of employees possessing the rating.

Re: Grouping Experience

Anonymous — Thu, 12 Sep 2013 12:42:11 GMT

Hi Arindam,

If you create a calculated dimention with Class, it will work as you is describing.

Use this as the dimention code: if ( Year >= 10, '10+', Class( Year, 5 ) )

You can also use this in a hierarchy, so you can do your Drill.

Re: Grouping Experience

swuehl — Thu, 12 Sep 2013 12:49:32 GMT

Since the Employee's experience is static, I would suggest to classify the experience in the script, though it should work also using a calculated dimension.

I haven't understood why you are using the

If Experience <= 5 then Load ... part

I would just use my code snippet above, and add your original fields EmpName, rating etc.:

LOAD

[Emp name],

Exp,

if(Exp <6, dual('1-5 yrs',5),

if(Exp < 11, dual('6-10 yrs', 10), dual('10+ yrs', 20) ) ) as ExpGroup,

rating,

..... // your other fields to be included here

FROM EmpDetail_New.xlsx;

It's probably easier to help you if you could post a small sample app or your excel file.

Re: Re: Grouping Experience

Thu, 12 Sep 2013 13:40:58 GMT

Thanks Swuehl,

I am attaching the file, your idea is working but it is adding a more line of experience,

Re: Re: Grouping Experience

swuehl — Thu, 12 Sep 2013 13:53:29 GMT

I would start with just 2 tables:

Tab1:

LOAD [Emp Name],

EmpID,

Address,

DOB,

Education,

Experience,

If(Experience<6,Dual('1-5 yrs', 5),

if(Experience<11,Dual( '6-10 yrs',10),dual('10+ yrs',20))) as ExpGroup

from

EmpDetail_New.xlsx

(ooxml, embedded labels, table is Data);

Tab2:

CrossTable(Year, Data)

LOAD EmpID,

[2009],

[2010],

[2011],

[2012],

[2013]

FROM

EmpDetail_New.xlsx

(ooxml, embedded labels, table is Rating);

These two tables will be linked by EmpID. This might be just ok, as far as I see.

Why do you want them concatenated into one table?

Re: Grouping Experience

Thu, 12 Sep 2013 14:43:14 GMT

hi Swuehl,

As I m a new guy to QV. I wanted to fetch the Rating of emps from other table. thats why I made concatenate. as here u have done crosstable, this was out of my mind. i have to try this. Thanx for all