If statement seems to be defaulting..?

[fanninam]

Does the following statement look accurate?  The reason I ask is because it keeps defaulting to whatever number I put at the end.

IF(COVERAGE_ID = '1', '90',
if(COVERAGE_ID = '2', '365',
if(COVERAGE_ID = '3', '9999',
if(COVERAGE_ID = '4', '365',
if(COVERAGE_ID = '5', '365',
if(COVERAGE_ID = '6', '731',
if(COVERAGE_ID = '7', '365',
if(COVERAGE_ID = '8', '365',
if(COVERAGE_ID = '9', '731',
if(COVERAGE_ID = '10', '1096',
if(COVERAGE_ID = '11', '9999',
if(COVERAGE_ID = '12', '365',
if(COVERAGE_ID = '13', '9999',
if(COVERAGE_ID = '14', '1096',
if(COVERAGE_ID = '84', '731',
if(COVERAGE_ID = '101', '1461',
if(COVERAGE_ID = '83', '365',
if(COVERAGE_ID = '121', '1826',
if(COVERAGE_ID = '141', '2556', '1096'
))))))))))))))))))) + TRX_DATE AS NEW_DATE

[vishsaggi]

What is this field here ? TRX_DATE

Is that a date field? What are you trying to add to the date? You requirement is not clear here?

You can use Pick(Match(COVERATE_ID, ...),....) something like this. Can you explain little more what you trying to achieve?

[fanninam]

I have also tried using the following Pick Match statement.  TRX_DATE is the invoice date.  The COVERAGE_ID is the number of warranty days.  The NEW_DATE is the warranty expiration date.

PICK(WILDMATCH(COVERAGE_ID,1,2,3,4,5,6,7,8,9,10,11,12,13,14,84,101,83,121,141,'*'),'90','365','9999','365','365','731','365','365','731','1096','9999','365','9999','1096','731','1461','365','1826','2556','9999') + TRX_DATE AS NEW_DATE

[vishsaggi]

Oh days you cant add like that, you need to use some date functions. Will work on it and get back.

[vishsaggi]

Try this:

Date(TRX_DATE + PICK(WILDMATCH(COVERAGE_ID,1,2,3,4,5,6,7,8,9,10,11,12,13,14,84,101,83,121,141,'*'),'90','365','9999','365','365','731','365','365','731','1096','

9999','365','9999','1096','731','1461','365','1826','2556','9999') ) AS NEW_DATE

[maxgro]

you can also use a mapping table

// test data

T:

load

  date(today() - rowno()) as TRX_DATE,

  ceil(rand()*5) as COVERAGE_ID

AutoGenerate 100;

// just 5 rows to test

MapWarrant:

Mapping load * inline [

From, To

1, 90

2, 365

3, 9999

4, 365

5, 365

];

F:

LOAD

  TRX_DATE,

  COVERAGE_ID,

  Date(TRX_DATE + ApplyMap('MapWarrant', COVERAGE_ID, 1096)) as NEW_DATE

Resident T;

DROP Table T;

[swuehl]

If you don't get any matches in your if() statements, then check how your COVERAGE_ID values actually look like?

Could you post some sample data or a small sample QVW?