topic Implementing multiple Decode statements in QV in QlikView

Implementing multiple Decode statements in QV

Thu, 09 Dec 2010 01:56:31 GMT

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

Implementing multiple Decode statements in QV

Thu, 09 Dec 2010 02:48:02 GMT

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

Implementing multiple Decode statements in QV

Oleg_Troyansky — Thu, 09 Dec 2010 07:15:16 GMT

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

Implementing multiple Decode statements in QV

Tue, 14 Dec 2010 03:21:48 GMT

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;

Implementing multiple Decode statements in QV

Oleg_Troyansky — Tue, 14 Dec 2010 04:00:10 GMT

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.

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