Create two fields from one

[darren_dixon]

Hi,

I have a field called Staff where I'd like to add their full name and respective department.

Staff

JohnA

JohnS

I was wondering if there was a way to do this, such as like the Dual function rather than two IF statements which would give the following ouput as I'd like to use them in a Multi Box.

Staff	Department
John Anderson	HR
John Smith	Finance

[Staff Details]:

LOAD "Staff" AS Staff,

     If(Staff='JohnA',Dual('John Anderson','HR'),

     If(Staff='JohnS',Dual('John Smith','Finance'),)) AS Department

RESIDENT [Staff Link]

;

Thanks,
Darren

[christian77]

Hi:

I don't see it very clear but I'll show you the function SUBFIELD

subfield('Jonh-Smith','-',1) & ' ' & subfield('Jonh-Smith','-',2) = Jonh Smith

That means you can break a field in several parts if the separator character is known. I've placed an n-dash this time but an space can be used as well.

Salutes.

[tresesco]

Dual would require a numeric parameter, therefore, in your case it would not help you. You can use pick() and match() in combination which would improve the performance compared to multiple IFs.

Try like:

Load

Pick(Match(Staff, 'JohnA','JohnS'), 'HR' , 'Finance') as Department

From <>;

[DavidFoster1]

Dual allows you to combine a numeric and text value together into a single field. It is not going to work in the way you need. You are going to need to have multiple fields.

[#5]

You can use mapping funcions:

//Create the Mapping Table

StaffMap:

Mapping

LOAD * INLINE [

    Staff, Department

    JohnS, HR

    JohnA, Finance

];

//Load your table

[Staff Details]:

LOAD "Staff"                                                     AS Staff,                             

          applymap('StaffMap',Staff,'Not Found')       AS Department

RESIDENT [Staff Link]

;