topic Data transformation - Basketball Dashboard in QlikView

Data transformation - Basketball Dashboard

Tue, 26 Mar 2013 13:43:22 GMT

Dear QlikView User,

I have been trying to solve the following issue since 2 days and could not find a way to do this. Neither did any solution in the Forum tackle this concrete issue.

Thus I really hope someone of you has any input on this.

Case: I want to display Basketball statistics in a QlikView Dashboard; aggregate Keyfigures, display charts and all that nice stuff one can do utilizing QlikView.

Problem:

Within my Dashboard I would like to display the winner of any given Basketball-Match.

The InputData is stored in seperate .csv files. Each file is build in the following fashion:

%PlayerID	%PlayerNo	Team	Name	Points	value 1	value 2	...	...	value N
430	E	A	TEAM	102	XXX	xxx	x		xx
421	E	B	TEAM	78	XXX	xxx	x	x	x
3145	23	A	Mike	23	...	...			...
3455	12	A	Thomas	3	...	..
4514	12	B	Peter	23	...	..
3154	5	B	Juan	0	...	..

The %PlayerID is Unique

%PlayerNo ist not unique but E indicates the aggregated Teamscore

Team is either A or B (in every table)

Name is the Players name

Points are the total points made by that player or Team.

I have another table storing the Game information like Date, Site, Time, Spectators, TeamA and TeamB

The Data model is a littlebit larger than this, but I try to simplify the matter in order to make it easier to comprehend.

I want to derive the winner by comparing the two values where Team = E and writing the higher one into another table.

My Approach:

I tried to implement a for each loop in the following fashion.

*****SKRIPT START:

for each vFile in FileList('Excel\*.csv')

test:
LOAD * FROM
$(vFile)
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Let vWinner = if(peek('Points',1,'test')>peek('Points',6,'test'),'TeamA','TeamB');

target:
LOAD
$(vWinner) as Winner;

Drop table test;
NEXT vFile

****** SCRIPT END

The first loop is executed perfectly, only to crash on the second loop with the following error:

Field not found - <TeamB>

test:

Any ideas what is wrong with the script or an alternative approach?

In the attachment you can find the full datamodell. (for some reason he duplicated some tables in the bottom right hand corner...you can just ignore those)

Thank you very much and have a nice day.

Greetings Thees

Re: Data transformation - Basketball Dashboard

swuehl — Tue, 26 Mar 2013 14:01:07 GMT

I think your script snippet is missing something, at least the target table load seems not to be a valid, complete statement (no source). You can try an autogenerate 1, if needed.

Also the error message looks kind of strange, since there is no field 'TeamB'' in your input tables / load script, right?

Could you post a small sample application and an input file?

Re: Data transformation - Basketball Dashboard

Tue, 26 Mar 2013 14:28:57 GMT

Thank you! Your answer gave me the correct hint. I indeed missed something.

Using a INLINE Table did the trick for me.

Now i load every table from the folder. Compare the two values and store the corresponding ID of the higher one into an inline table.

for each vFile in FileList('Excel\*.csv')

test:
LOAD * FROM
$(vFile)
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Let vWinner = if(peek('PTS',0,'test')>peek('PTS',5,'test'),'TeamA','TeamB');

Let vWinnerID = peek('SPID',if($(vWinner)='TeamA',0,5),'test');

target:
LOAD * INLINE [
SPID, Winner
$(vWinnerID), $(vWinner)
];

Drop table test;
NEXT vFile

Is There a more efficient / nice way to solve this?

Thank you very much, just writing the problem down and thinking about your response already helped a lot