Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #353: A Pokémon Tournament

RWvanLeeuwen
11 - Bolide

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
16 - Nebula
16 - Nebula

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

Ray_Pospisil
8 - Asteroid

Solution without JSON tool in developer

hfin047
8 - Asteroid

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

karlhansen
9 - Comet
Spoiler
solution_353.jpg
Raees09
8 - Asteroid

Nice challenge.

Raees09_0-1673460839955.png

 

cgoodman3
14 - Magnetar
14 - Magnetar
Spoiler
cgoodman3_0-1673470218778.png

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

Chris
Check out my collaboration with fellow ACE Joshua Burkhow at AlterTricks.com
cgoodman3
14 - Magnetar
14 - Magnetar

Designer Cloud Solution

Spoiler
cgoodman3_0-1673472498859.png


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.

Chris
Check out my collaboration with fellow ACE Joshua Burkhow at AlterTricks.com
mg46
8 - Asteroid

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

 

Spoiler
mguarniere_0-1673476351163.png

 

sergejs_kutkovics
9 - Comet

My try )

Spoiler
sergejs_kutkovics_0-1673513076896.png