Implementing multiple Decode statements in QV

[#1]

Hi All,

We are migrating a reporting application from Discoverer to Qlikview, In one of the reports the discoverer uses multiple Decode statements in order to acheive the business purpose and provide the correct data. I have the following piece of code in Discoverer:

DECODE(o116396. REGION,'JPN','OJM','JFG','OJM','US','FRE',

DECODE((

DECODE(NVL(o163832. SHIP_FROM_ORG_ID,99),23,'GLO',28,'OIM',29,'OUR',33,'OJS',34,'OJM',37

,'ODM',38,'FRE',39,'VMM',61,'JCI',62,'JSC',81,'UCI',82,'ECI',101,'OCF',161,'JDM',181,'UDS',202

,'ODS',221,'BEI',441,'CSS',241,'PCM',322,'PBU','442','OPS',361,'VMP',99,' ',CONCAT('NOT MAPPED: Org ID ',TO_CHAR(o163832. SHIP_FROM_ORG_ID)))

),'JCI','OJM','Unknown'))) as C_2

Please suggest what is the best way to implement this is qlikview, Any sample code in QV will be highly appreciated.

Regards,

Kingshuk

,

[#2]

The easiest way would be to embed that as a field in your load script, passing the DECODE statement to Oracle in your SQL statement. That way you don't have to change a thing (assuming that it already works).

Otherwise you'll have to do something like this with QV if's, with many more levels. Replace 'bla...' with many more if's.

if(o116396. REGION='JPN','OJM',

if(o116396. REGION='JFG','OJM',

if(o116396. REGION='US','FRE','bla...')

)

) as C_2

[Oleg_Troyansky]

I'm not totally sure how DECODE syntax works, but it looks like you could use QlikView Mapping tables to achieve similar results.

[#4]

For example:

You could use the decode function in an SQL statement as follows:

SELECT supplier_name,
decode(supplier_id,	10000,	'IBM',
	10001,	'Microsoft',
	10002,	'Hewlett Packard',
		'Gateway') result
FROM suppliers;

The above decode statement is equivalent to the following IF-THEN-ELSE statement:

IF supplier_id = 10000 THEN
result := 'IBM';

ELSIF supplier_id = 10001 THEN
result := 'Microsoft';

ELSIF supplier_id = 10002 THEN
result := 'Hewlett Packard';

ELSE
result := 'Gateway';

END IF;

[Oleg_Troyansky]

Yes, exactly - MAPPING TABLE is the QlikView way of achieving the same result - you load your pais of "decode" values into a mapping table, and then you use function ApplyMap() to perform the "decoding" of a specific value.