If Statement bringing up wrong output

[#1]

Hi All,

   can anyone help me with this codes below:

LOAD*,

   If(Match(TICKET_STATUS, 'WORK IN PROGRESS', 'HOLD', 'ASSIGNED', 'NEW'),

   If(TIER = 'GOLD',

   IF(Now() > TimeStamp#(SubField(RESOLVE_BEFORE, ' ', 1) & ' ' & Trim(Mid(RESOLVE_BEFORE, Len(SubField(RESOLVE_BEFORE, ' ', 1)) + 1)), 'MMMM DD, YYYY hh:mm:ssTT'),'OUT_OF_SLA','IN_SLA'),

   IF(TIER = 'SILVER',

   IF(DECODE_PRIORITY ='High' AND NOW()>LastWorkDate(TimeStamp#(SubField(RESOLVE_BEFORE, ' ', 1) & ' ' & Trim(Mid(RESOLVE_BEFORE, Len(SubField(RESOLVE_BEFORE, ' ', 1)) + 1)), 'MMMM DD, YYYY hh:mm:ssTT'),2),'OUT_OF_SLA',

   IF(DECODE_PRIORITY='Medium' AND NOW()>LastWorkDate(TimeStamp#(SubField(RESOLVE_BEFORE, ' ', 1) & ' ' & Trim(Mid(RESOLVE_BEFORE, Len(SubField(RESOLVE_BEFORE, ' ', 1)) + 1)), 'MMMM DD, YYYY hh:mm:ssTT'),7),'OUT_OF_SLA',

   IF(DECODE_PRIORITY='Low' AND NOW()>LastWorkDate(TimeStamp#(SubField(RESOLVE_BEFORE, ' ', 1) & ' ' & Trim(Mid(RESOLVE_BEFORE, Len(SubField(RESOLVE_BEFORE, ' ', 1)) + 1)), 'MMMM DD, YYYY hh:mm:ssTT'), 10),'OUT_OF_SLA','IN_SLA')))))) as OPEN_SLA;

LOAD *,

   If(Match(TICKET_STATUS, 'WORK IN PROGRESS', 'HOLD', 'ASSIGNED', 'NEW'),

   If(TIER = 'GOLD',

   IF(Now() > TimeStamp#(SubField(RESOLVE_BEFORE, ' ', 1) & ' ' & Trim(Mid(RESOLVE_BEFORE, Len(SubField(RESOLVE_BEFORE, ' ', 1)) + 1)), 'MMMM DD, YYYY hh:mm:ssTT'),'OUT_OF_SLA','IN_SLA'),

   If(TIER = 'BRONZE',

   IF(Now() > LastWorkDate(TimeStamp#(SubField(RESOLVE_BEFORE, ' ', 1) & ' ' & Trim(Mid(RESOLVE_BEFORE, Len(SubField(RESOLVE_BEFORE, ' ', 1)) + 1)), 'MMMM DD, YYYY hh:mm:ssTT'), 40),'OUT_OF_SLA','IN_SLA'),

  IF(TIER = 'SILVER',

   IF(DECODE_PRIORITY ='High' AND NOW()>LastWorkDate(TimeStamp#(SubField(RESOLVE_BEFORE, ' ', 1) & ' ' & Trim(Mid(RESOLVE_BEFORE, Len(SubField(RESOLVE_BEFORE, ' ', 1)) + 1)), 'MMMM DD, YYYY hh:mm:ssTT'),2),'OUT_OF_SLA',

   IF(DECODE_PRIORITY='Medium' AND NOW()>LastWorkDate(TimeStamp#(SubField(RESOLVE_BEFORE, ' ', 1) & ' ' & Trim(Mid(RESOLVE_BEFORE, Len(SubField(RESOLVE_BEFORE, ' ', 1)) + 1)), 'MMMM DD, YYYY hh:mm:ssTT'),7),'OUT_OF_SLA',

   IF(DECODE_PRIORITY='Low' AND NOW()>LastWorkDate(TimeStamp#(SubField(RESOLVE_BEFORE, ' ', 1) & ' ' & Trim(Mid(RESOLVE_BEFORE, Len(SubField(RESOLVE_BEFORE, ' ', 1)) + 1)), 'MMMM DD, YYYY hh:mm:ssTT'), 10),'OUT_OF_SLA','IN_SLA'))))))) as OPEN_SLA;

===================================================================================================

The first LOAD statement works, but the second works but bring up the wrong set of selections and the highlighted line is the only difference between them. stalwar1‌   Sunny pls help me with this. Thanks

[#2]

stalwar1‌vinieme12‌ PLEASE GUYS HELP ME WITH THIS.

[sunny_talwar]

Would you be able to show what you get and what you expected to get?

[#4]

when TICKET_STATUS=GOLD and NOW()>RESOLVE_BEFORE

the output is meant to be OUT_OF_SLA, but it  shows IN_SLA.

The same applies to when TICKET_STATUS = SILVER.

The first load statement works perfectly, but as soon as I added the highlighted line, it started bringing out wrong output.

[sunny_talwar]

Why do you have two similar load statements? I am not sure I completely understand?

[#6]

I added this line below to the second load statement, it isn't in the first load statement. That's the only different.

If(TIER = 'BRONZE',

   IF(Now() > LastWorkDate(TimeStamp#(SubField(RESOLVE_BEFORE, ' ', 1) & ' ' & Trim(Mid(RESOLVE_BEFORE, Len(SubField(RESOLVE_BEFORE, ' ', 1)) + 1)), 'MMMM DD, YYYY hh:mm:ssTT'), 40),'OUT_OF_SLA','IN_SLA'),

[sunny_talwar]

I don't see anything wrong in your if statement.... may be if you can share a sample I might be able to dig a little more

[sunny_talwar]

Also, the output is only incorrect for TIRE = 'BRONZE', right?

[#9]

Hi Sunny,

  Pls find the attached.

Nope, the output is incorrect for all the TIRES.

[sunny_talwar]

Which tab am I looking and can you point me to a row which isn't coming as your expect?