If Statement, Mapping Load or other method to handle this use case

[#1]

I have Field XYZ with Values A, B, C, D. This field is used as a key List Box on an existing app.

I need to keep this field and add a new field that groups these values. So:

Field TUV with Values A, B AS Test1 and Values C, D as Test2

** so if I select Test1, then values from A and B will return and if I select Test2, values from C and D will return

What is the best way to do this, either by leveraging an 'If' statement or mapping load or another method.

I would greatly appreciate exact syntax leveraging my mock example. Thanks all!

[sunny_talwar]

May be this:

Method1:

Mapping_Table:

Mapping

LOAD * Inline [

Field1, Field2

A, Test1

B, Test1

C, Test2

D, Test2

];

Fact:

LOAD [Field XYZ],

           ApplyMap('Mapping_Table', [Field XYZ]) as [Grouped Field]

FROM Source;

Method2:

Fact:

LOAD [Field XYZ],

           If(Match([Field XYZ], 'A', 'B'), 'Test1', 'Test2') as [Grouped Field]

FROM Source;

[maxgro]

if you only have few values (4 in your question, A B C D) you can use an if or a pick(match(...)...)

Load

       *,

       pick(match(XYZ, 'A', 'B', 'C', 'D'), 'Test1', 'Test1', 'Test2', 'Test2') as TUV;

Load

       *,

       XYZ

       ....

[#4]

Thanks for the quick reply. I have tried this method and my script reloads successfully, the new field is added, however, no values are present to select (was hoping for two values: StaffAug and Project. My exact logic is below (minus the logic after this piece to load everything else). Any initial thoughts of what I might have done wrong? note: I am working from an existing, very over engineered script so the root cause could be deeper.

Load *,

  pick(match([Financial Tracking Level], 'A', 'AB', 'ABFT', 'ABC'),

  'StaffAug', 'StaffAug', 'Project', 'Project') AS [Project Type];

[sunny_talwar]

Script looks fine to me. I think it might be another issue like you said.

[maxgro]

with your data

Load *,

  pick(match([Financial Tracking Level], 'A', 'AB', 'ABFT', 'ABC'),

  'StaffAug', 'StaffAug', 'Project', 'Project') AS [Project Type];

Load * inline [

Financial Tracking Level

A

AB

ABFT

ABC

ZZZ1

ZZZ2

];

[#7]

Hi - I just wanted to let you know that the original syntax you provided did end up working -- I will store this for future reference! My values were actually 1. A, 2. AB, 3. ABFT, 4. ABC, so I just needed to adjust those values in the script. Thanks again for your help. I am working a large project so I might have additional questions if I get stuck, but thanks so much.