Multiple IF in variable

[mdj3366]

Hello,

Using CR11, SQL database. Using the below formula to return a date.
WhileReadingRecords;
DateVar Array Holidays;
DateVar Target:=date({payment.updt_datetime});
NumberVar Add:= 5;
NumberVar Added := 0;

WHILE Added < Add
Do (target := target +1;
if dayofweek (target) in 2 to 6 and not (target in holidays)
then Added:=Added+1
else Added:=Added);
Target

Is it possible to add multiple IF statements to return multiple dates based upon a specific field? Thanks.

 

[lbass]

You haven't really explained what you want to do here. Maybe show some sample data and then show what you expect the formula results to be. Identify the specific field and explain why you want a different date per this field.

-LB

 

[mdj3366]

Hi LB,
What i which to accomplish is this: in the formula posted earlier, i want to be able to add more than one NumberVar Add:= 5 command so that i can have a different date returned based upon a specified company. Something like this:

if company=1 then NumberVar Add:= 6 else
if company=2 then NumberVar Add:= 7
the returned date should be a business day, and if it's a holday, then closest previous business day.

Hope this helps..

 

[lbass]

Try:

WhileReadingRecords;
DateVar Array Holidays;
DateVar Target:=date({payment.updt_datetime});
NumberVar Add;
NumberVar Added := 0;

if company=1 then
Add := 6 else
if company=2 then
Add := 7 else
Add := 5; //some default
WHILE Added < Add
Do (target := target +1;
if dayofweek (target) in 2 to 6 and not (target in holidays)
then Added:=Added+1
else Added:=Added);
Target

-LB

 

[mdj3366]

The report I have looks like this:

IL 06/12/2009 2,384.62 0.00 2,384.62 Paid 6/15/2009 5:57:35 PM 6/22/2009
IL 06/19/2009 2,980.77 0.00 2,980.77 Eligible 6/15/2009 4:02:58 PM 6/22/2009
IL 06/26/2009 2,980.77 0.00 2,980.77 Eligible 6/22/2009 5:47:36 AM 6/29/2009

Our database creates a record for each call, with each call returning a check date. Each person must call once per week. If someone is paid on a biweekly schedule, then there will be 2 calls with the same check date. When I added the new formula, i get this:
Name payment_date wages UI payment_amt status Date Processed Check Date
IL 06/12/2009 2,384.62 0.00 2,384.62 Paid 6/15/2009 5:57:35 PM 6/22/2009
IL 06/12/2009 2,384.62 0.00 2,384.62 Paid 6/15/2009 5:57:35 PM 6/22/2009
IL 06/19/2009 2,980.77 0.00 2,980.77 Eligible 6/15/2009 4:02:58 PM 6/22/2009
IL 06/19/2009 2,980.77 0.00 2,980.77 Eligible 6/15/2009 4:02:58 PM 6/22/2009
IL 06/26/2009 2,980.77 0.00 2,980.77 Eligible 6/22/2009 5:47:36 AM 6/29/2009
IL 06/26/2009 2,980.77 0.00 2,980.77 Eligible 6/22/2009 5:47:36 AM 6/29/2009
There is only one payment date for each week, but it is returning two.

 

[lbass]

Are you saying that changing this formula caused the duplicate rows? I don't see why it would. Could you simply format the fields to suppress if duplicated?

-LB

 

[mdj3366]

If i suppress the fields, the same number of incorrect records are returned, but now i have white space where the data was. This report is grouped on name, division and payment.update. I have the payment amounts sum by payment.update so that i can see the gross payment amount for the processing period, by person. Could it be the linking that is causing the trouble? i have 4 tables - employee, group, payment and calllog. The employeeid in the employee table is linked to the employeeid in the payment table. employeegroup in employee is linked to employeegroup in group table. employee ssn links to calllog ssn. Seems like each payment date is inflated by exactly the number of calls that have come in for each person, ie. John Doe called in 10 times, now his payment for 3/10 is listed as 10 duplicate payments, the same for his 3/17 payment and so on.

 

[lbass]

Well, yes, the calllog table is creating the duplicate records. If you are grouping on payment update (same as payment_date?), then place the fields in that group header or footer and suppress the details section. Then use running totals instead of inserted summaries, and in the evaluation section, check evaluate on change of group: payment update. The rt should be placed in footer sections, a separate rt for each level of result (if you want one per name, reset on change of name; if at the grand total level, reset never).

-LB