Want to get involved? We're always looking for ideas and content for Weekly Challenges.
SUBMIT YOUR IDEAHi @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 :-)
Hi @RWvanLeeuwen ! Thank you for pointing that out ! Just added the screenshot. Although I don't know why it is sthat small ! :)
Solution without JSON tool in developer
Only done the desktop version for now as haven't worked out how to parse json in Designer Cloud
Designer Cloud Solution
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.
My try )