nexted if in prepare recipe

Options
Scobbyy2k3
Scobbyy2k3 Partner, Registered Posts: 26 Partner

please, how do I parse this in the prepare recipe . I keep getting errors

if(LBTESTCD = VLDLCP3","VLDL & Chylomicron Particles",
if(LBTESTCD = VLCP3","Large VLDL & Chylomicron Particles",
if(LBTESTCD = VMP3","Medium VLDL Particles",
if(LBTESTCD = VSP3","Small VLDL Particles",
if(LBTESTCD = LDLP3","LDL Particles",
if(LBTESTCD = IDLP3","IDL Particles",
if(LBTESTCD = LLP3","Large LDL Particles",
if(LBTESTCD = LSP3","Small LDL Particles",
if(LBTESTCD = HDLP3","HDL Particles",
if(LBTESTCD = HLP3","Large HDL Particles",
if(LBTESTCD = HMP3","Medium HDL Particles",
if(LBTESTCD = HSP3","Small HDL Particles",
if(LBTESTCD = VZ3","VLDL Size",
if(LBTESTCD = LZ3","LDL Size",
if(LBTESTCD = HZ3","HDL Size",
if(LBTESTCD = LPIR","Lipoprotein Insulin Resistance Score",
if(LBTESTCD = ELP_TC","Total Cholesterol",
if(LBTESTCD = ELP_HDLC","HDL Cholesterol",
if(LBTESTCD = ELP_TG","Triglycerides",
if(LBTESTCD = ELP_APOB","ApoB",
if(LBTESTCD = ELP_LDLC","LDL Cholesterol",
if(LBTESTCD = ELP_VLDLC","VLDL Cholesterol",
if(LBTESTCD = ELP_NONHDLC","Non HDL Cholesterol")))))))))))))))))))))))

Tagged:

Answers

  • Manuel
    Manuel Alpha Tester, Dataiker Alumni, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Dataiku DSS Adv Designer, Registered Posts: 193 ✭✭✭✭✭✭✭
    Options

    Hi,

    I suggest you use a reference data table to define those relationships, rather than hardcode them in a complex if statement:

    • Add an editable dataset to your flow (+dataset > Editable)
    • Edit two columns and add your relationships as rows to your editable dataset;
    • Do a left join between your original table and the editable dataset, to translate between the two values

    Your flow should look similar to the attached image.

    I hope this helps.

  • Jurre
    Jurre Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS Core Concepts, Registered, Dataiku DSS Developer, Neuron 2022 Posts: 114 ✭✭✭✭✭✭✭
    Options

    Great suggestion @Manuel
    , a lot more manageable that way!

    @Scobbyy2k3
    it looks like something is missing in your formula : take for example the first line : if(LBTESTCD = VLDLCP3","VLDL & Chylomicron Particles",

    Change that to if(LBTESTCD == "VLDLCP3","VLDL & Chylomicron Particles",

    >> the comparison operator is == and there seems to be missing a double quote in every line.

    Hope this helps!

    Jurre

  • Manuel
    Manuel Alpha Tester, Dataiker Alumni, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Dataiku DSS Adv Designer, Registered Posts: 193 ✭✭✭✭✭✭✭
    Options

    Having such logic on a nested if statement also has an impact on performance:

    • The if statement on a formula processor is executed in the DSS server, for every row in the dataset
    • A join of two datasets in the same connection can be pushed down to an SQL engine

    Best regards

  • Scobbyy2k3
    Scobbyy2k3 Partner, Registered Posts: 26 Partner
    Options

    Thank you all, i have it fixed

Setup Info
    Tags
      Help me…