topic Re: Create two fields from one in QlikView

Create two fields from one

darren_dixon — Thu, 26 Sep 2013 12:49:18 GMT

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

Re: Create two fields from one

christian77 — Thu, 26 Sep 2013 13:33:41 GMT

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.

Re: Create two fields from one

tresB — Thu, 26 Sep 2013 13:42:01 GMT

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

Re: Create two fields from one

DavidFoster1 — Thu, 26 Sep 2013 13:48:08 GMT

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.

Re: Create two fields from one

Thu, 26 Sep 2013 14:12:35 GMT

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]

;