Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Make 1 field from 3 fields

I have three fields in an Excel file: OTR, ISO, IMDL.

OTR has values of  DOUG, BILL, TERI, CHICAGO, MEXICO, DALLAS

ISO has values of  MARK, PAUL, TONY, RAY

IMDL has values of   RYAN, PETER, JOHN

I want to make one field called Assigned Pricers (the field will be used

as a dropdown box to allow my dashboard users to select the Pricer).

I want it to look like the following:

DOUG

BILL

TERI

CHICAGO

MEXICO

DALLAS

MARK

PAUL

TONY

RAY

RYAN

PETER

JOHN

14 Replies
Clever_Anjos
Employee
Employee

These fields are on same table?

Not applicable
Author

Yes. In the same Excel file, QVD.

Clever_Anjos
Employee
Employee

All that fields are filled?

For example:

If OTR is 'doug' , ISO is wmpty or has a value?

maxgro
MVP
MVP

see attachment

Not applicable
Author

The fields are valued as my example shows. However,

the fields are valued as such on the excel file.

OTR ISO IMDL

MARK

DOUG RYAN

BILL PAUL

TERI

CHICAGO TONY PETER

MEXICO JOHN

DALLAS RAY

Not applicable
Author

I see the results are the way I need them, but I can’t see the script you created to get to that point.

MarcoWedel

Test data for script design

maxgro
MVP
MVP

script:

- a test table, Transactions with your data (OTR, ISO,MDL)

- a table with [Assigned Pricers] field

Transactions:

load *, rowno() as id;

Load

TransLineID,

TransID,

mod(TransID,26)+1 as Num,

Pick(Ceil(6*Rand1),'DOUG', 'BILL', 'TERI', 'CHICAGO', 'MEXICO', 'DALLAS') as OTR,

Pick(Ceil(4*Rand1),'MARK', 'PAUL', 'TONY', 'RAY') as ISO,

Pick(Ceil(3*Rand()),'RYAN', 'PETER', 'JOHN') as IMDL,

Round(1000*Rand()*Rand()*Rand1) as Expression1;

Load

Rand() as Rand1,

IterNo() as TransLineID,

RecNo() as TransID

Autogenerate 1000

While Rand()<=0.5 or IterNo()=1;

Table:

load distinct id, OTR as [Assigned Pricers] Resident  Transactions;

Concatenate (Table) load distinct id, ISO as [Assigned Pricers] Resident  Transactions;

Concatenate (Table) load distinct id, IMDL as [Assigned Pricers] Resident  Transactions;

user interface

1.png

maxgro
MVP
MVP

script

see attachment for script (I already posted it but is currently being moderated so you can't see it)

user interface

1.png