Data Transformation Help Needed

[#1]

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

[MK_QSL · Accepted solution]

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;

[stigchel]

There are some excellent articles on Hierarchy (created by Henric  Cronstrom) that may help you. See attached document

[MarcoWedel]

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

[#5]

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!

[MarcoWedel]

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

[MarcoWedel]

Or

Where not Wildmatch(Team,'Number*', 'Participating*', 'Geo') and IsAssigned;