DSS Formula

Data123
Level 1
DSS Formula
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
0 Kudos
3 Replies
apichery
Dataiker
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'))
0 Kudos
Data123
Level 1
Author
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 !
0 Kudos
apichery
Dataiker
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.
0 Kudos