nexted if in prepare recipe

[Scobbyy2k3]

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")))))))))))))))))))))))

[Manuel]

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]

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]

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]

Thank you all, i have it fixed