topic Data Transformation Help Needed in QlikView

Data Transformation Help Needed

Thu, 16 Oct 2014 15:25:06 GMT

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

Re: Data Transformation Help Needed

MK_QSL — Thu, 16 Oct 2014 15:29:47 GMT

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;

Re: Data Transformation Help Needed

stigchel — Thu, 16 Oct 2014 16:23:25 GMT

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

Re: Data Transformation Help Needed

MarcoWedel — Thu, 16 Oct 2014 23:09:01 GMT

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

Re: Data Transformation Help Needed

Fri, 31 Oct 2014 17:07:12 GMT

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!

Re: Data Transformation Help Needed

MarcoWedel — Fri, 31 Oct 2014 20:06:16 GMT

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

Re: Data Transformation Help Needed

MarcoWedel — Fri, 31 Oct 2014 20:10:58 GMT

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