Challenge #353: A Pokémon Tournament

[RWvanLeeuwen]

Hi @Jean-Balteryx ,
Would you kindly share an image of the workflow you built? After opening your workflow I can no longer use Designer :-( but I want to know what you solution looks like :-)

[Jean-Balteryx]

Hi @RWvanLeeuwen ! Thank you for pointing that out ! Just added the screenshot. Although I don't know why it is sthat small ! :)

[Ray_Pospisil]

Solution without JSON tool in developer

[hfin047]

Not sure if the cleanest process. but was good to practice with the transpose tool.

[karlhansen]

Saqueador

[Raees09]

Nice challenge.

[cgoodman3]

Saqueador

Only done the desktop version for now as haven't worked out how to parse json in Designer Cloud

[cgoodman3]

Designer Cloud Solution

Saqueador

Two recipes
1) Identify trainers with 8 skills
splitrows col: column1 on: '\n'
header
splitpatterns col: {name,pokemons} type: on on: ','
flatten col: {name,pokemons2}
columnbyexample columns: {name,pokemons1} value: CASE([ISNULL({name,pokemons1}),NULL(),ISMISSING([{name,pokemons1}]),'',SUBSTRING({name,pokemons1}, FINDNTH({name,pokemons1}, /[A-Z]+/, 1, right, false), LEN({name,pokemons1}) - LEN(SUBSTITUTE(SUBSTRING({name,pokemons1}, FINDNTH({name,pokemons1}, /[A-Za-z0-9]+/, 1, left, false), LEN({name,pokemons1})), /[A-Za-z0-9]+/, '', false, /^/)))]) exampleMappingId: 1 as: 'name,pokemons3'
drop col: {name,pokemons1} action: Drop
extractbetweendelimiters col: {name,pokemons2} start: ':' end: ','
rename type: manual mapping: [{name,pokemons1},'pokemon number']
extractbetweendelimiters col: {name,pokemons2} start: '\"' end: '\"}'
drop col: {name,pokemons2} action: Drop
rename type: manual mapping: [{name,pokemons1},'pokemon name']
join with: Pokedex.csv col: name = Pokedex.csv.name,{name,pokemons3} = current_dataset.{name,pokemons3},{pokemon number} = current_dataset.{pokemon number},type_1 = Pokedex.csv.type_1,type_2 = Pokedex.csv.type_2 row: current_dataset.{pokemon name} == Pokedex.csv.name type: inner
unpivot col: type_2,type_1 groupEvery: 1
groupby group: {name,pokemons3},value type: agg
filter type: custom rowType: single row: ISMISSING([value]) action: Delete
groupby group: {name,pokemons3} value: COUNT() type: agg
filter type: custom rowType: single row: 8 <= row_count action: Keep

2)Structure output table
splitrows col: column1 on: '\n'
header
splitpatterns col: {name,pokemons} type: on on: ','
flatten col: {name,pokemons2}
columnbyexample columns: {name,pokemons1} value: CASE([ISNULL({name,pokemons1}),NULL(),ISMISSING([{name,pokemons1}]),'',SUBSTRING({name,pokemons1}, FINDNTH({name,pokemons1}, /[A-Z]+/, 1, right, false), LEN({name,pokemons1}) - LEN(SUBSTITUTE(SUBSTRING({name,pokemons1}, FINDNTH({name,pokemons1}, /[A-Za-z0-9]+/, 1, left, false), LEN({name,pokemons1})), /[A-Za-z0-9]+/, '', false, /^/)))]) exampleMappingId: 1 as: 'name,pokemons3'
drop col: {name,pokemons1} action: Drop
extractbetweendelimiters col: {name,pokemons2} start: ':' end: ','
rename type: manual mapping: [{name,pokemons1},'pokemon number']
extractbetweendelimiters col: {name,pokemons2} start: '\"' end: '\"}'
drop col: {name,pokemons2} action: Drop
rename type: manual mapping: [{name,pokemons1},'pokemon name']
join with: Identify trainers with more than 8 skills col: {name,pokemons3} = current_dataset.{name,pokemons3},{pokemon name} = current_dataset.{pokemon name},{pokemon number} = current_dataset.{pokemon number} row: current_dataset.{name,pokemons3} == Identify trainers with more than 8 skills.{name,pokemons3} type: inner
columnbyexample columns: {pokemon number} value: CASE([ISNULL({pokemon number}),NULL(),ISMISSING([{pokemon number}]),'',MERGE(['pokemon_',{pokemon number}])]) exampleMappingId: 2 as: 'pokemon number1'
pivot col: {pokemon number1} group: {name,pokemons3} value: ANY({pokemon name}) limit: 50
rename type: findAndReplace col: {any_pokemon name_pokemon_2}, {any_pokemon name_pokemon_3}, {any_pokemon name_pokemon_4}, {any_pokemon name_pokemon_5}, {any_pokemon name_pokemon_6}, {any_pokemon name_pokemon_1} on: 'any_pokemon name_' with: ''
rename type: manual mapping: [{name,pokemons3},'trainer_name']

I could have probably made this simpler with breaking the first recipe into two, so the first 10 steps don't need duplicating.

[mg46]

This one was fun!  I got a little RegEx practice in.

Saqueador

[sergejs_kutkovics]

My try )

Saqueador