Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: Make 1 field from 3 fields

These fields are on same table?

Not applicable

Re: Make 1 field from 3 fields

Yes. In the same Excel file, QVD.

Employee
Employee

Re: Make 1 field from 3 fields

All that fields are filled?

For example:

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

MVP
MVP

Re: Make 1 field from 3 fields

see attachment

Not applicable

Re: Make 1 field from 3 fields

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

Re: Make 1 field from 3 fields

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

Re: Make 1 field from 3 fields

Test data for script design

MVP
MVP

Re: Make 1 field from 3 fields

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

MVP
MVP

Re: Re: Make 1 field from 3 fields

script

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

user interface

1.png