Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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;
Mine is the smallest
Lav det ordentligt i en SQL database og group by dig ud af den og join navnet på med max længde
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
Hi Nicolai,
PFA for your scenario.
Hope it helps!!
Regards,
Yojas