sum problem

[nicolai_moller]

Hi

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.

[swuehl]

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.

[Peter_Cammaert]

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

[ronaldocarrijo]

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;

[Peter_Cammaert]

Mine is the smallest

[#6]

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

[#7]

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

[#8 · Accepted solution]

Hi Nicolai,

PFA for your scenario.

Hope it helps!! 

Regards,

Yojas