0 votes
Hi everyone,

How would i create a column called 'Group' using DSS Formula based on a number of factors i have tried below but having issues

if(prof=='X', target=='true', active=='true', click==1, download==1, 'GroupA')

 

Either DSS Formula or SQL query is fine - i would preferably want to create multiple 'Groups' in one column though - e.g.

if((prof=='X', target=='true', active=='true', click==1, download==1, 'GroupA', if(prof=='X', target=='false', active=='true', click==1, download==1, 'GroupB'))

 

Thanks
asked by

1 Answer

0 votes
The correct syntax is the following for a multiple if/then/else with multiple ands:

if (and(Sex == 'male', and(Age > 30, Pclass == 3)), 'GroupMaleOldRich',
  if (and(Sex == 'male', and(Age <= 30, Pclass == 3)), 'GroupMaleYoungRich',
    if (and(Sex == 'male', and(Age > 30, Pclass <= 2)), 'GroupMaleOldPoor',
      if (and(Sex == 'male', and(Age <= 30, Pclass <= 2)), 'GroupMaleYoungPoor',
          'GroupOther'))))

 

So in your case, that would give:

if(and(prof=='X',and(target=='true',and(active=='true', and(click==1, download==1)))), 'GroupA',
  if(and(prof=='X',and(target=='false',and(active=='true', and(click==1, download==1)))), 'GroupB',
  'GroupOther'))
answered by
Thanks for this - your formula is valid however, in one column its just stating 'GroupOther'  for all values (not the ones specified)  - i've also tried with just creating one test Group ( if(and(prof=='X', and(target=='true' ,active=='true')), 'groupA')  - i keep getting the expect 3 arguments error

Does formatting/type of column affect the formula i.e string, boolean etc ?

Thank you so much !
The syntax of the if instruction is
if(boolean, expression_true, expression_false)

So you need 3 arguments
(Read https://doc.dataiku.com/dss/5.0//advanced/formula.html for more info)

If you don't want to name your last group, just use an empty string ('') instead of 'GroupOther'.

Formatting/type of column does not affect the formula if you consider everything as strings.
971 questions
998 answers
1,047 comments
2,361 users

┬ęDataiku 2012-2018 - Privacy Policy