topic Re: sum problem in QlikView

sum problem

nicolai_moller — Mon, 28 Oct 2013 13:28:34 GMT

I have a set of data where I'm trying to calculate the number of hours each employee has worked. Each employee have an employee number, but in some cases an employee will have more than one name (dont know why) as seen in the table below

Name	Employee no.	Year	Hours worked
Roger Fe	101	2012	1000
Roger Fede	101	2013	500
Roger Federer	101	2013	500
Novak Djoko	102	2012	700
Novak Djokovic	102	2013	500
Rafael Nadal	103	2013	600

I want a pivot table that sums the hours for each employee but only displays the name with most characters (as in the table below).

Name	Hours worked
Roger Federer	2000
Novak Djokovic	1200
Rafael Nadal	600

Thanks.

Re: sum problem

swuehl — Mon, 28 Oct 2013 13:37:39 GMT

Maybe like this:

Names:

LOAD Name,

[Employee no.],

Year,

[Hours worked]

FROM

[http://community.qlik.com/thread/97325]

(html, codepage is 1252, embedded labels, table is @1);

TMP:

LOAD [Employee no.],

len(Name) as Len,

Name

Resident Names;

Left Join (Names) LOAD

[Employee no.],

FirstSortedValue(Name, -Len) as ConsolName

Resident TMP group by [Employee no.];

drop table TMP;

Then use ConsolName as dimension in your chart.

Re: sum problem

Peter_Cammaert — Mon, 28 Oct 2013 13:39:44 GMT

If employee names only reside in your table with hours worked, create a small table in your script with just Employee No and MaxString(Name) columns from your facts, like in:

MaxNames:

LOAD [Employee no.], MaxString(Name) AS LongName

RESIDENT Facts

GROUP BY [Employee no.];

Then LEFT JOIN the small table to the facts table. Now your rows will have a LongName field that you can use in your Pivot table.

Actually, you don't even have to join these tables, as LongName can be used as Dimension in any case.

Peter

Re: sum problem

ronaldocarrijo — Mon, 28 Oct 2013 13:40:44 GMT

Use this:

tbMapName:

Mapping
LOAD
[Employee no.],
MaxString(Name) AS NameX
FROM uploadfile

GROUP BY [Employee no.];

Table:
LOAD
ApplyMap('tbMapName',[Employee no.]) AS Name,

[Employee no.],

Year,

[Hours worked]

FROM uploadfile;

Re: sum problem

Peter_Cammaert — Mon, 28 Oct 2013 13:43:55 GMT

Mine is the smallest

Re: sum problem

Mon, 28 Oct 2013 13:44:25 GMT

Lav det ordentligt i en SQL database og group by dig ud af den og join navnet på med max længde

Re: sum problem

Mon, 28 Oct 2013 13:48:22 GMT

Hi,

I think the best way to keep the details and to have a clear name is the ronaldo's solution with the Applymap but there is an error in the script :

You must add a Group By like this :

tbMapName:

Mapping
LOAD
[Employee no.],
MaxString(Name) AS NameX
FROM uploadfile

group by [Employee no.];

No need to join anything.

Hope this help.

Bertrand

Re: sum problem

Mon, 28 Oct 2013 14:17:50 GMT

Hi Nicolai,

PFA for your scenario.

Hope it helps!!

Regards,

Yojas