topic Re: How can I structure this Data more clearly in QlikView

How can I structure this Data more clearly

rustyfishbones — Thu, 21 Aug 2014 14:34:25 GMT

I have the following Data in a Text File,

How can structure more easily, it contains details of a Cricket game between teams and I want to analyse the files of everygame, but the files are in the below format, what's the best approach

Record=Match

Type=1

Date=18/08/2014

Ground=7;Castle Avenue

Grade=11;RSA Alan Murray

Club1=18;Terenure

Club2=4;Clontarf CC

Team1=54;Terenure 1

Team2=4;Clontarf CC Clontarf 1

Captain1=170;S Moreton

Captain2=34;E Delany

Keeper1=752;M Hogan

Keeper2=35;R Forrest

Config=6;1;1;1;0

Home=2

Toss=1

Result=2

Innings=1

Batsman1=170;S Moreton;3;35;R Forrest;12;Z Van Merwe;0;0;1;0;0;1;0

Batsman2=753;D Langford Smith;2;0;;186;J Morrissey;8;15;3;2;0;6;0

Batsman3=498;R Jones;3;35;R Forrest;12;Z Van Merwe;5;15;4;1;0;9;0

Batsman4=232;C Dickeson;3;39;M Collier;12;Z Van Merwe;0;15;2;0;0;1;0

Batsman5=227;C Kelly;3;186;J Morrissey;42;C D'Arcy;22;27;6;4;0;19;0

Batsman6=752;M Hogan;3;37;A Poynter;42;C D'Arcy;7;50;5;1;0;6;0

Batsman7=1363;C Morgan;9;35;R Forrest;890;P Ryan;13;52;8;1;0;18;0

Batsman8=499;U Jehangir;2;0;;42;C D'Arcy;0;68;9;0;0;2;0

Batsman9=492;B Hoey;2;0;;36;B Coghlan;10;76;10;0;1;9;0

Batsman10=1481;D Hogan;2;0;;12;Z Van Merwe;2;78;7;0;0;6;0

Batsman11=316;A O'Neill;1;0;;0;;1;0;0;0;0;6;0

Bowler1=12;Z Van Merwe;3;2;4;2;1;0

Bowler2=186;J Morrissey;2;0;1;19;0;0

Bowler3=36;B Coghlan;3;0;1;18;1;0

Bowler4=42;C D'Arcy;3;0;3;24;1;0

Bowler5=890;P Ryan;2.5;0;1;9;0;0

Legbyes=4

Byes=2

Wides=4

Noballs=0

Penalties=0

IResult=1

Innings=2

Batsman1=186;J Morrissey;3;227;C Kelly;499;U Jehangir;4;6;1;0;0;7;0

Batsman2=37;A Poynter;1;0;;0;;35;26;3;5;1;35;0

Batsman3=502;A D 'Arcy;6;0;;316;A O'Neill;4;37;4;0;0;8;0

Batsman4=138;A Nazir;9;752;M Hogan;170;S Moreton;6;38;5;1;0;7;0

Batsman5=34;E Delany;3;753;D Langford Smith;316;A O'Neill;1;71;6;0;0;3;0

Batsman6=36;B Coghlan;8;492;B Hoey;498;R Jones;11;0;0;0;0;13;0

Batsman7=35;R Forrest;1;0;;0;;10;0;0;2;0;5;0

Batsman8=12;Z Van Merwe;0;0;;0;;0;0;0;0;0;0;0

Batsman9=39;M Collier;0;0;;0;;0;0;0;0;0;0;0

Batsman10=890;P Ryan;0;0;;0;;0;0;0;0;0;0;0

Batsman11=42;C D'Arcy;0;0;;0;;0;0;0;0;0;0;0

Bowler1=227;C Kelly;3;0;0;9;0;0

Bowler2=499;U Jehangir;2;0;1;15;2;0

Bowler3=316;A O'Neill;2;0;2;15;0;0

Bowler4=170;S Moreton;3;0;1;13;0;0

Bowler5=492;B Hoey;2;0;0;15;0;0

Bowler6=498;R Jones;1;0;0;10;0;0

Legbyes=2

Byes=2

Wides=6

Noballs=0

Penalties=0

IResult=2

Endmatch=True

/***/ CStatz Export End

Re: How can I structure this Data more clearly

Qlik_Trigg — Thu, 21 Aug 2014 19:28:33 GMT

Alan

Ah a cricket problem!! Caught my eye straight away. Can you provide some metadata of each line in the scorecard (looks like player ID, name, method of dismissal, fielder, bowler, score, then probably 4's, 6's, balls faced. Also the bowling analysis

Thanks

John

Re: How can I structure this Data more clearly

rustyfishbones — Fri, 22 Aug 2014 07:39:17 GMT

Hi John,

I don't have the metadata quite yet, I am still waiting on it.

I need to structure it so I can have the txt file in rows and Columns, but as you can see the scorecard contains details of both teams so I am not sure how I can structure it easily.

I will have thousands of these files for all matches played, so if I can load one correctly the rest should work fine too

I really hope you can help me, I am trying to help a friend with this, he currently collates all the information into Excel with pivot tables and all sorts of manual workarounds

Regards

Alan

Re: How can I structure this Data more clearly

flipside — Fri, 22 Aug 2014 08:07:48 GMT

There's a link to the actual game scorecard on Clontarf's website which answers most of the data. I would post the link but it forces moderation.

I'm assuming it was a T20 game!!

Re: How can I structure this Data more clearly

jagan — Fri, 22 Aug 2014 08:25:53 GMT

Hi Alan,

This is the link for the match

totalcricketscorer.com/TCSLive/TCSScorecard.aspx?id=27047

Regards,

Jagan.

Re: How can I structure this Data more clearly

rustyfishbones — Fri, 22 Aug 2014 08:39:29 GMT

Excellent, at least I have the MetaData for the file.

Now if I can just use the txt file to structure the data better

Re: How can I structure this Data more clearly

marcus_sommer — Fri, 22 Aug 2014 10:56:45 GMT

I think this approach could be helpful: QlikTip #11: Einlesen von Ini-Dateien

- Marcus

Re: How can I structure this Data more clearly

rustyfishbones — Fri, 22 Aug 2014 11:08:28 GMT

I will look at this Marcus

Thanks

Alan