Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
darren_dixon
New Contributor III

Create two fields from one

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.

StaffDepartment
John AndersonHR
John SmithFinance

[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

4 Replies
Partner
Partner

Re: Create two fields from one

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.

MVP
MVP

Re: Create two fields from one

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

dfoster9
Valued Contributor

Re: Create two fields from one

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.

Not applicable

Re: Create two fields from one

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]

;