topic Re: If statement seems to be defaulting..? in QlikView

If statement seems to be defaulting..?

fanninam — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: If statement seems to be defaulting..?

vishsaggi — Thu, 02 Feb 2017 17:11:31 GMT

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?

Re: If statement seems to be defaulting..?

fanninam — Thu, 02 Feb 2017 17:21:29 GMT

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

Re: If statement seems to be defaulting..?

vishsaggi — Thu, 02 Feb 2017 17:30:20 GMT

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

Re: If statement seems to be defaulting..?

vishsaggi — Thu, 02 Feb 2017 19:30:22 GMT

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

Re: If statement seems to be defaulting..?

maxgro — Thu, 02 Feb 2017 19:47:29 GMT

you can also use a mapping table

// test data

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

];

LOAD

TRX_DATE,

COVERAGE_ID,

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

Resident T;

DROP Table T;

Re: If statement seems to be defaulting..?

swuehl — Thu, 02 Feb 2017 20:11:05 GMT

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?