topic Re: If statement with Subfield in QlikView

If statement with Subfield

Wed, 05 Feb 2014 16:25:27 GMT

Hi,

I have an aggregated data which I need to separate the data by ":" and indicate them as "Sector", "Region" "Country" and "Business". However, not all data are in the same order. For example, some data have Sector/Region/Country/Business and some data have Sector/Region/Business/Region, Column "KeyInd" indicates the difference. I've constructed following if statement but unable to finish it. Please help.

The logic is If KeyInd = 'TB061_tblKeys_Business', then the third value after ":" will be Business and if not, and if KeyInd = 'TB029_tblKeys_Countries', then the fourth value after ":" will be Country, if not Business)

LOAD

Key_ID,

SubField(Key_ID, ':', 1) AS AML_Sector,
SubField(Key_ID, ':', 2) AS AML_Region,
If(KeyInd = 'TB061_tblKeys_Business', SubField(Key_ID, ':', 3)As AML_Business, AML_Country),
If(KeyInd = 'TB029_tblKeys_Countries', SubField(Key_ID, ':', 4)As AML_Country, AML_Business),

KeyInd

FROM
QVD\AML_Master.qvd
(qvd);

Thank you!

Jasmine

Re: If statement with Subfield

Nicole-Smith — Wed, 05 Feb 2014 16:28:26 GMT

LOAD Key_ID,

SubField(Key_ID, ':', 1) AS AML_Sector,

SubField(Key_ID, ':', 2) AS AML_Region,

if(KeyInd = 'TB061_tblKeys_Business', SubField(Key_ID, ':', 3), SubField(Key_ID, ':', 4)) as AML_Business,

if(KeyInd = 'TB029_tblKeys_Countries', SubField(Key_ID, ':', 4), SubField(Key_ID, ':', 3)) as AML_Country,

KeyInd

FROM

C:\Users\jl99043\Desktop\Indicator.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: If statement with Subfield

Wed, 05 Feb 2014 16:53:28 GMT

Hi Nicole,

Thank you for your quick response.

Jasmine