String Functions to Split Field into Multiple Fields

[shaunsomai]

I have Chess Notation which I get via and api call, this is then loaded into a qlik app, I then have a field called game moves , I would like to split this out to this format

MoveNumber, WhiteMove, BlackMove

1, a3,e6

2,h3,d5

......

Here is the notation for one record 

1. a3 {[%clk 0:00:29.1]} 1... e6 {[%clk 0:00:29.6]} 2. h3 {[%clk 0:00:28.9]} 2... d5 {[%clk 0:00:29.2]} 3. f4 {[%clk 0:00:28.5]} 3... Nf6 {[%clk 0:00:28.2]} 4. Nf3 {[%clk 0:00:28.2]} 4... Qd6 {[%clk 0:00:26.6]} 5. d4 {[%clk 0:00:27]} 5... Ne4 {[%clk 0:00:25.5]} 6. Nc3 {[%clk 0:00:25.1]} 6... Ng3 {[%clk 0:00:24.4]} 7. Rg1 {[%clk 0:00:23.6]} 7... Nxf1 {[%clk 0:00:23.2]} 8. Kxf1 {[%clk 0:00:22.4]} 8... c5 {[%clk 0:00:22.7]} 9. dxc5 {[%clk 0:00:20.9]} 9... Qxc5 {[%clk 0:00:22.3]} 10. e4 {[%clk 0:00:19.6]} 10... dxe4 {[%clk 0:00:21.1]} 11. Nxe4 {[%clk 0:00:18.6]} 11... Qb6 {[%clk 0:00:19.6]} 12. Nfd2 {[%clk 0:00:15.3]} 12... Bc5 {[%clk 0:00:17.6]} 13. Nc4 {[%clk 0:00:14.3]} 13... Bxg1 {[%clk 0:00:16.4]} 14. Nxb6 {[%clk 0:00:12.5]} 14... Bxb6 {[%clk 0:00:15.8]} 15. Bd2 {[%clk 0:00:11]} 15... Bd7 {[%clk 0:00:15.2]} 16. Bb4 {[%clk 0:00:10.7]} 16... Nc6 {[%clk 0:00:14.2]} 17. Nd6+ {[%clk 0:00:09.8]} 17... Kd8 {[%clk 0:00:12.7]} 18. Nxb7+ {[%clk 0:00:08.6]} 18... Kc7 {[%clk 0:00:11.6]} 19. Nd6 {[%clk 0:00:08.4]} 19... Rhd8 {[%clk 0:00:10.3]} 20. Nb5+ {[%clk 0:00:07.7]} 20... Kb7 {[%clk 0:00:09]} 21. Qd6 {[%clk 0:00:06.5]} 21... a6 {[%clk 0:00:07.9]} 22. Qc7+ {[%clk 0:00:05.2]} 22... Bxc7 {[%clk 0:00:06.5]} 23. Nxc7 {[%clk 0:00:03.5]} 23... Kxc7 {[%clk 0:00:06]} 24. Ba5+ {[%clk 0:00:02.8]} 24... Nxa5 {[%clk 0:00:04.7]} 25. b4 {[%clk 0:00:01.4]} 25... Rab8 {[%clk 0:00:04]} 26. Rd1 {[%clk 0:00:01.1]} 26... Nc4 {[%clk 0:00:02.8]} 27. Rc1 {[%clk 0:00:00.7]} 27... Bc6 {[%clk 0:00:02.5]} 28. c3 {[%clk 0:00:00.3]} 28... Rd1+ {[%clk 0:00:01.9]} 0-1

[MarcoWedel]

maybe something along the lines of:

mapSplitMoves:
Mapping
LOAD ' '&RecNo()&'. ',
     ' <Next_Move>'&RecNo()&'. '
AutoGenerate 10000;

tabGames:
LOAD RecNo() as GameID,
     GameMoves  
Inline "
GameMoves
1. a3 {[%clk 0:00:29.1]} 1... e6 {[%clk 0:00:29.6]} 2. h3 {[%clk 0:00:28.9]} 2... d5 {[%clk 0:00:29.2]} 3. f4 {[%clk 0:00:28.5]} 3... Nf6 {[%clk 0:00:28.2]} 4. Nf3 {[%clk 0:00:28.2]} 4... Qd6 {[%clk 0:00:26.6]} 5. d4 {[%clk 0:00:27]} 5... Ne4 {[%clk 0:00:25.5]} 6. Nc3 {[%clk 0:00:25.1]} 6... Ng3 {[%clk 0:00:24.4]} 7. Rg1 {[%clk 0:00:23.6]} 7... Nxf1 {[%clk 0:00:23.2]} 8. Kxf1 {[%clk 0:00:22.4]} 8... c5 {[%clk 0:00:22.7]} 9. dxc5 {[%clk 0:00:20.9]} 9... Qxc5 {[%clk 0:00:22.3]} 10. e4 {[%clk 0:00:19.6]} 10... dxe4 {[%clk 0:00:21.1]} 11. Nxe4 {[%clk 0:00:18.6]} 11... Qb6 {[%clk 0:00:19.6]} 12. Nfd2 {[%clk 0:00:15.3]} 12... Bc5 {[%clk 0:00:17.6]} 13. Nc4 {[%clk 0:00:14.3]} 13... Bxg1 {[%clk 0:00:16.4]} 14. Nxb6 {[%clk 0:00:12.5]} 14... Bxb6 {[%clk 0:00:15.8]} 15. Bd2 {[%clk 0:00:11]} 15... Bd7 {[%clk 0:00:15.2]} 16. Bb4 {[%clk 0:00:10.7]} 16... Nc6 {[%clk 0:00:14.2]} 17. Nd6+ {[%clk 0:00:09.8]} 17... Kd8 {[%clk 0:00:12.7]} 18. Nxb7+ {[%clk 0:00:08.6]} 18... Kc7 {[%clk 0:00:11.6]} 19. Nd6 {[%clk 0:00:08.4]} 19... Rhd8 {[%clk 0:00:10.3]} 20. Nb5+ {[%clk 0:00:07.7]} 20... Kb7 {[%clk 0:00:09]} 21. Qd6 {[%clk 0:00:06.5]} 21... a6 {[%clk 0:00:07.9]} 22. Qc7+ {[%clk 0:00:05.2]} 22... Bxc7 {[%clk 0:00:06.5]} 23. Nxc7 {[%clk 0:00:03.5]} 23... Kxc7 {[%clk 0:00:06]} 24. Ba5+ {[%clk 0:00:02.8]} 24... Nxa5 {[%clk 0:00:04.7]} 25. b4 {[%clk 0:00:01.4]} 25... Rab8 {[%clk 0:00:04]} 26. Rd1 {[%clk 0:00:01.1]} 26... Nc4 {[%clk 0:00:02.8]} 27. Rc1 {[%clk 0:00:00.7]} 27... Bc6 {[%clk 0:00:02.5]} 28. c3 {[%clk 0:00:00.3]} 28... Rd1+ {[%clk 0:00:01.9]} 0-1
1. e4 {[%clk 1:30:56]} 1... c5 {[%clk 1:30:55]} 2. c3 {[%clk 1:31:19]} 2... Nf6 {[%clk 1:31:16]} 3. e5 {[%clk 1:31:42]} 3... Nd5 {[%clk 1:31:43]}
1. e4 {[%clk 1:30:11]} 1... e6 {[%clk 1:30:51]} 2. d4 {[%clk 1:30:23]} 2... d5 {[%clk 1:31:16]} 3. Nc3 {[%clk 1:30:37]} 3... dxe4 {[%clk 1:30:19]}
";

tabGameMoves:
LOAD *,
     SubField(Move,'.',1) as MoveNumber,
     TextBetween(Move,'. ',' {',1) as WhiteMove,
     TextBetween(Move,'... ',' {',1) as BlackMove;
LOAD GameID,
     SubField(MapSubString('mapSplitMoves',GameMoves),'<Next_Move>') as Move
Resident tabGames;