IF condition formula

[Anonymous]

Hi,

Please help building the below IF condition for the below table, to calculate T and O.

IF "first O Amount  = 0 or 9999"  then give "second  O Amount". IF  "second  O Amount = 0 or 9999" then give "Last  O Amount". IF "Last  O Amount = 0 or 9999" then Ignore. and show as Blank.

IF "first O Amount" NOT Equal 0 or 9999 then calculate "Report Date - first Date".

IF "second O Amount" NOT Equal 0 or 9999 then calculate "Report Date - second Date".

IF "Last O Amount" NOT Equal 0 or 9999 then calculate "Report Date - Last Date".

IF "first T Amount = 0 or 4999" then give "second  T Amount". IF "second  T Amount" = 0 or 4999" then give "Last  T Amount". IF "Last  T Amount" = 0 or 4999" then Ignore and show as Blank.

IF "first T Amount" NOT Equal 0 or 4999 then calculate "Report Date - first Date".

IF "second T Amount" NOT Equal 0 or 4999 then calculate "Report Date - second  Date".

IF "Last T Amount" NOT Equal 0 or 9999 then calculate "Report Date - Last Date".

[oknotsen]

I will give you the first one. I hope that makes you understand how to make the others.

if(FieldA = 0 or FieldA = 9999,
if(FieldB = 0 or FieldB = 9999,
if(FieldC = 0 or FieldC = 9999,
'',
FieldC)
, FieldB)
, FieldA)

Obviously replace FieldA, B, and C with the names you used.

[settu_periasamy]

Hi,

find the attached.

LOAD [Report Date], 
     [first Date], 
     [first O Amount], 
     [first T Amount], 
     [second  Date], 
     [second  O Amount], 
     [second  T Amount], 
     [Last  Date], 
     [Last  O Amount], 
     [Last  T Amount], 

    if([first O Amount]=0 or [first O Amount]=9999,
      [second  O Amount],    
      if([second  O Amount]=0 or [second  O Amount]=9999,
      [Last  O Amount],    
      if([Last  O Amount]=0 or [Last  O Amount]=9999,''))) as O_Amount,

    if([first T Amount]=0 or [first T Amount]=4999,
      [second  T Amount],    
      if([second  T Amount]=0 or [second  T Amount]=4999,
      [Last  T Amount],    
      if([Last  T Amount]=0 or [Last  T Amount]=4999,''))) as T_Amount,

  if([first O Amount] <>0 or [first O Amount] <>9999,
  num([Report Date])-num([first Date]), 
      if([second  O Amount] <>0 or [second  O Amount] <>9999,
      num([Report Date])-num([second  Date]),
      if([Last  O Amount] <>0 or [Last  O Amount] <>9999,num([Report Date])-num([Last  Date])))) as O_Date,

    if([first T Amount] <>0 or [first T Amount] <>4999,
      num([Report Date])-num([first Date]),      
      if([second  T Amount] <>0 or [second  T Amount] <>4999,
      num([Report Date])-num([second  Date]),    
      if([Last  T Amount] <>0 or [Last  T Amount] <>4999,num([Report Date])-num([Last  Date])))) as T_Date
FROM
[T and O.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Anonymous]

I have applied the above given formula. However, I keep getting an error " Field names must be unique within table"

I have checked each name and the are all inique!! Can not really know what is the issue ?

Just to recap:

IF T does not equal zero or 4999 then show the following value.

IF T equal a value then (first date - report date )

Same is applied to O calculation

Below is the original script I used.

[Report Date],

     [Int Claim Id],

     [Int Claim No],

     [Ind Col Code],

     Inc_Amount,

     [Pol Cl E Desc],

     [Pol Insured Name],

     [Os Amount],

     Recovery,

     Salvage,

     [OS after Recovery],

     [PARTIAL/TOTAL LOSS],

     [Incurred amount],

     [Loc Code],

     [OD/TP],

     [Date Of Reporting],

     [Ind Serial No],

     Reporting_date,

     [Date of LPO],

     [Date of Payment],

     [OD definition],

     [TP definition],

     [Claim Amount],

     [Loc Desc],

     [Int Status],

     [ODTP Definition],

     [Prep Emp Name],

     [Set Settlement Date],

     [Set Settlemed Amount],

     [Set Paid Amount],

     [Set OS Flag],

     [Stt E Desc],

     [Set Settlement Type],

     Set_OS_Amount,

     [Intimation Date],

     [Intimated Loss OD Amount],

     [Intimated Loss TP Amount],

     [First Assessment Date],

     [First Assessment OD Amount],

     [First Assessment TP Amount],

     [Last Assessment Date],

     [Last Assessment OD Amount],

     [Last Assessment TP Amount],

     [TP to Date],

     [OD to Date],

    

    

     if([Intimated Loss OD Amount]=0 or [Intimated Loss OD Amount]=9999,[First Assessment OD Amount],    

if([First Assessment OD Amount]=0 or [First Assessment OD Amount]=9999,[Last Assessment OD Amount],    

if([Last Assessment OD Amount]=0 or [Last Assessment OD Amount]=9999,''))) as [OD Blank],

if([Intimated Loss TP Amount]=0 or [Intimated Loss TP Amount]=4999,[First Assessment TP Amount],    

if([First Assessment TP Amount]=0 or [First Assessment TP Amount]=4999,[Last Assessment TP Amount],    

if([Last Assessment TP Amount]=0 or [Last Assessment TP Amount]=4999,''))) as [TP Blank],

   

if([Intimated Loss OD Amount] <>0 or [Intimated Loss OD Amount] <>9999,num([Report Date])-num([Intimation Date]),

if([First Assessment OD Amount] <>0 or [First Assessment OD Amount] <>9999,num([Report Date])-num([First Assessment Date]),

if([Last Assessment OD Amount] <>0 or [Last Assessment OD Amount] <>9999,num([Report Date])-num([Last Assessment Date])))) as [OD to Date],

if([Intimated Loss TP Amount] <>0 or [Intimated Loss TP Amount] <>4999,num([Report Date])-num([Intimation Date]),      

if([First Assessment TP Amount] <>0 or [First Assessment TP Amount] <>4999,num([Report Date])-num([First Assessment Date]),    

if([Last Assessment TP Amount] <>0 or [Last Assessment TP Amount] <>4999,num([Report Date])-num([Last Assessment Date])))) as [TP to Date] 

[veidlburkhard · Accepted solution]

Hi Faisalaaa,

I think this is because you are using the field names [TP to Date]  and [OD to Date] twice.

Regards

Burkhard

[settu_periasamy]

Hi,

As said by Burkhard, you need to remove

    [TP to Date],

    [OD to Date]

-it's duplicated

or change the field name for your if condition.

[jagan]

Hi,

You have used this field names twice ([TP to Date],  [OD to Date]) in the Load statement, rename any one of the columns and you won't get this error.

[Report Date],

     [Int Claim Id],

     [Int Claim No],

     [Ind Col Code],

     Inc_Amount,

     [Pol Cl E Desc],

     [Pol Insured Name],

     [Os Amount],

     Recovery,

     Salvage,

     [OS after Recovery],

     [PARTIAL/TOTAL LOSS],

     [Incurred amount],

     [Loc Code],

     [OD/TP],

     [Date Of Reporting],

     [Ind Serial No],

     Reporting_date,

     [Date of LPO],

     [Date of Payment],

     [OD definition],

     [TP definition],

     [Claim Amount],

     [Loc Desc],

     [Int Status],

     [ODTP Definition],

     [Prep Emp Name],

     [Set Settlement Date],

     [Set Settlemed Amount],

     [Set Paid Amount],

     [Set OS Flag],

     [Stt E Desc],

     [Set Settlement Type],

     Set_OS_Amount,

     [Intimation Date],

     [Intimated Loss OD Amount],

     [Intimated Loss TP Amount],

     [First Assessment Date],

     [First Assessment OD Amount],

     [First Assessment TP Amount],

     [Last Assessment Date],

     [Last Assessment OD Amount],

     [Last Assessment TP Amount],

     [TP to Date] AS [TP to Date Actual],

     [OD to Date] AS [OD to Date Actual],
     if([Intimated Loss OD Amount]=0 or [Intimated Loss OD Amount]=9999,[First Assessment OD Amount],    

if([First Assessment OD Amount]=0 or [First Assessment OD Amount]=9999,[Last Assessment OD Amount],    

if([Last Assessment OD Amount]=0 or [Last Assessment OD Amount]=9999,''))) as [OD Blank],

if([Intimated Loss TP Amount]=0 or [Intimated Loss TP Amount]=4999,[First Assessment TP Amount],    

if([First Assessment TP Amount]=0 or [First Assessment TP Amount]=4999,[Last Assessment TP Amount],    

if([Last Assessment TP Amount]=0 or [Last Assessment TP Amount]=4999,''))) as [TP Blank],

   

if([Intimated Loss OD Amount] <>0 or [Intimated Loss OD Amount] <>9999,num([Report Date])-num([Intimation Date]),

if([First Assessment OD Amount] <>0 or [First Assessment OD Amount] <>9999,num([Report Date])-num([First Assessment Date]),

if([Last Assessment OD Amount] <>0 or [Last Assessment OD Amount] <>9999,num([Report Date])-num([Last Assessment Date])))) as [OD to Date],

if([Intimated Loss TP Amount] <>0 or [Intimated Loss TP Amount] <>4999,num([Report Date])-num([Intimation Date]),      

if([First Assessment TP Amount] <>0 or [First Assessment TP Amount] <>4999,num([Report Date])-num([First Assessment Date]),    

if([Last Assessment TP Amount] <>0 or [Last Assessment TP Amount] <>4999,num([Report Date])-num([Last Assessment Date]))))as [TP to Date]