If statement with Subfield

[#1]

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

[Nicole-Smith · Accepted solution]

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);

[#3]

Hi Nicole,

Thank you for your quick response.

Jasmine