conditional functions

[#1]

Hi,

I have a table that contains date, billed hrs, po category, rate etc. Now, depending on the PO Category (1, 2 3 and 4) i have to use different calculation methods to calculate billed amounts. Category 1 and 3 means that rates are maintained at PO level (blanket rates). If the category is 1, then it is hourly rate but if 3 then it is monthly rate so i have to use different logic to get the billed amount. then there can be rate changed on a certain date that are mantained in another table (po_rc) so I have to check that table to check if there was a rate change for the PO and if it was I have to use that rate after the change effective date. po category 2 and 4 means that the rate is maintained at employee level and I have to check a different table for the employee rate change. etc. etc.

as you can see there are multiple and nested conditions. is there a way to achieve these kind of scenarios in the load script? is there a way to use the following kind of logic?

if ........

if........

elseif........

endif.

endif.

any suggestions are welcome.

thanks & regards,

manoj agrawal

[#2 · Accepted solution]

Hi Manoj,

I hope you want to know the syntax to write multiple if else stateents. This is how you write it.

if(condition, FCTrue if(secondCondition,Then value, else value),FC False if(third condition, then value, else value))

FC stand for First condition. ie. if FC is true, here you must give the nested condition. This is how it is done. This is a bit confusing, but this can be easily done using set analysis. If you want more info on set analysis, go to the reference manual and search for set analysis. Hope this helps you.

thanks Joseph........

[#3]

Hi Joesph,

Thanks for replying!

I have been trying to use this syntax but it keeps giving me some error or the other. please check the code below

FACT:
LOAD distinct ts_timesheetdate,
ProjCode,
ts_regtime,
ts_overtime,
ts_timeoff,
ts_holiday,
ts_admin,
ts_bench,
ts_admintimeoff,
ts_timeoffreasonind,
ts_unpaidtimeoff,
EmpCode,
PO,
proj_startdate,
proj_enddate,
PO_Catg,
po_startdate,
po_enddate,
//po_regrate,
//po_otrate,
if(PO_Catg = 1 or 3, if(ts_timesheetdate >= po_effectivedate, po_newregrate, po_regrate), if(ts_timesheetdate >= proj_effectivedate, proj_newregrate, po_regrate)) as po_regrate,
//if(PO_Catg = 1 or 3, if(ts_timesheetdate >= po_effectivedate, po_newotrate, po_otrate),if(ts_timesheetdate => proj_effectivedate, proj_newotrate, po_otrate) ) as po_otrate,
//if(PO_Catg = 3 or 4, (ts_regtime + ts_holiday) * po_regrate/MonthTotalHrs,(ts_regtime*po_regrate + ts_overtime*po_otrate)) as billed_amt,
Project,
Project_name,
Client
RESIDENT Timesheets;

Calendar:

LOAD DISTINCT
ts_timesheetdate,
Year(ts_timesheetdate) as Year,
Month(ts_timesheetdate) as Month,
Week(ts_timesheetdate) as Week,
Month(ts_timesheetdate) &Year(ts_timesheetdate) AS MonthYear,
Applymap('MonthQuarter', num(Month(ts_timesheetdate))) as Quarter,
inYearToDate(ts_timesheetdate, $(varToday), 0) * -1 as CurYTDFlag,
inYearToDate(ts_timesheetdate, $(varToday), -1) * -1 as LastYTDFlag
Resident FACT;

when i try to reload this, it has started giving me an error (missing ')') on my calendar table. if i uncomment the second if condition, it fails on the FACT table load giving me the same missing ')' error.

i am going crazy with this!

regards,

manoj agrawal

[boorgura]

Manoj,

Try this:

FACT:
LOAD distinct ts_timesheetdate,
ProjCode,
ts_regtime,
ts_overtime,
ts_timeoff,
ts_holiday,
ts_admin,
ts_bench,
ts_admintimeoff,
ts_timeoffreasonind,
ts_unpaidtimeoff,
EmpCode,
PO,
proj_startdate,
proj_enddate,
PO_Catg,
po_startdate,
po_enddate,
//po_regrate,
//po_otrate,
if(PO_Catg = 1 or PO_Catg = 3, if(ts_timesheetdate >= po_effectivedate, po_newregrate, po_regrate), if(ts_timesheetdate >= proj_effectivedate, proj_newregrate, po_regrate)) as po_regrate,
//if(PO_Catg = 1 or 3, if(ts_timesheetdate >= po_effectivedate, po_newotrate, po_otrate),if(ts_timesheetdate => proj_effectivedate, proj_newotrate, po_otrate) ) as po_otrate,
//if(PO_Catg = 3 or 4, (ts_regtime + ts_holiday) * po_regrate/MonthTotalHrs,(ts_regtime*po_regrate + ts_overtime*po_otrate)) as billed_amt,
Project,
Project_name,
Client
RESIDENT Timesheets;

Calendar:

LOAD DISTINCT
ts_timesheetdate,
Year(ts_timesheetdate) as Year,
Month(ts_timesheetdate) as Month,
Week(ts_timesheetdate) as Week,
Month(ts_timesheetdate) &Year(ts_timesheetdate) AS MonthYear,
Applymap('MonthQuarter', num(Month(ts_timesheetdate))) as Quarter,
inYearToDate(ts_timesheetdate, $(varToday), 0) * -1 as CurYTDFlag,
inYearToDate(ts_timesheetdate, $(varToday), -1) * -1 as LastYTDFlag
Resident FACT;

Let me know if it works!!!

[#5]

Hi Rocky,

Thanks for the reply!

Looks like PO_Catg = 1 or PO_Catg = 3 and PO_Catg = 1 or 3 are one at the same thing as it did not make a difference.

However, I was getting an error while loading it in debugger mode. I used a normal load and it loaded fine. Strange!

regards,

manoj agrawal

[#6]

Hey Rocky,

Though (PO_Catg = 1 or PO_Catg = 3) did not solve the problem of the error that I was getting but it does give correct results as compared to the statement (PO_Catg = 1 or 3).

thanks,

manoj agrawal