Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik Community,
I have a spreadsheet that maintains information on what working teams people are assigned to. Below is an example but the real sheet is many more rows (people) and columns (teams and people attributes).
Sort Level | Name | GEO | Team 1 | Team 2 | Team 3 | Team 4 | Team 5 | Number of Teams | Participating on Team? |
1-Nat | Glenn Rice | GEO2 | 1 | 1 | 1 | Yes | |||
2-Reg | Trey Burke | GEO24 | 1 | 1 | 2 | Yes | |||
1-Nat | Jalen Rose | GEO2 | 1 | 1 | Yes | ||||
2-Reg | Barry Sanders | GEO24 | 1 | 1 | Yes | ||||
4-Terr | Kirk Gibson | GEO246A2 | 1 | 1 | Yes | ||||
4-Terr | Chris Speilman | GEO246A4 | 1 | 1 | 2 | Yes | |||
3-Div | Herman Moore | GEO216 | 1 | 1 | Yes | ||||
4-Terr | Calvin Johnson | GEO216A1 | 1 | 1 | Yes | ||||
4-Terr | Tom Brady | GEO216B1 | 1 | 1 | Yes | ||||
2-Reg | Desmond Howard | GEO21 | 1 | 1 | Yes |
While I am loading this data as is, which works for the analytics needs in qlik, I would like to also have the data loaded so that I can report from a table that looks like this:
Name | Team |
Glenn Rice | Team 1 |
Jalen Rose | Team 1 |
Barry Sanders | Team 1 |
Herman Moore | Team 1 |
Tom Brady | Team 1 |
Trey Burke | Team 2 |
Kirk Gibson | Team 2 |
Chris Speilman | Team 2 |
Calvin Johnson | Team 2 |
Desmond Howard | Team 2 |
Trey Burke | Team 4 |
Chris Speilman | Team 4 |
Glen Rice | Team 5 |
Can I do this via transformation? I am limited in what I can do on the original input.
Thanks for any help that you can offer!
Sam
Temp:
LOAD [Sort Level],
Name,
GEO,
[Team 1],
[Team 2],
[Team 3],
[Team 4],
[Team 5],
[Number of Teams],
[Participating on Team?]
FROM
[http://community.qlik.com/thread/138134]
(html, codepage is 1252, embedded labels, table is @1);
Temp2:
CrossTable(Team,Data)
Load
Name,
[Team 1],
[Team 2],
[Team 3],
[Team 4],
[Team 5]
Resident Temp;
NoConcatenate
Final:
Load Name,Team Resident Temp2 Where Len(Trim(Data))>0;
Drop Table Temp2;
Drop Table Temp;
Temp:
LOAD [Sort Level],
Name,
GEO,
[Team 1],
[Team 2],
[Team 3],
[Team 4],
[Team 5],
[Number of Teams],
[Participating on Team?]
FROM
[http://community.qlik.com/thread/138134]
(html, codepage is 1252, embedded labels, table is @1);
Temp2:
CrossTable(Team,Data)
Load
Name,
[Team 1],
[Team 2],
[Team 3],
[Team 4],
[Team 5]
Resident Temp;
NoConcatenate
Final:
Load Name,Team Resident Temp2 Where Len(Trim(Data))>0;
Drop Table Temp2;
Drop Table Temp;
There are some excellent articles on Hierarchy (created by Henric Cronstrom) that may help you. See attached document
Hi,
one solution without hard coding team names could be:
tabTeamAssignement:
LOAD *
FROM [http://community.qlik.com/thread/138134] (html, codepage is 1252, embedded labels, table is @1);
tabTeamName:
CrossTable (Team, IsAssigned, 2)
LOAD * Resident tabTeamAssignement;
Right Join (tabTeamName)
LOAD Name, Team
Resident tabTeamName
Where Team like 'Team*' and IsAssigned;
DROP Fields [Sort Level], IsAssigned From tabTeamName;
hope this helps
regards
Marco
Thanks Marco,
Great process but in my example, I replaced teh real team names with Team 1, Team 2 etc. I might actually put "Team" in front of all my team moving forward though!
Thanks Again!
Then make it:
Where not Team like 'Number*' and not Team like 'Participating*' and not Team like 'Geo' and IsAssigned;
Hope this helps
Regards
Marco
Or
Where not Wildmatch(Team,'Number*', 'Participating*', 'Geo') and IsAssigned;