topic Re: Make 1 field from 3 fields in QlikView

Make 1 field from 3 fields

Wed, 04 Jun 2014 13:48:38 GMT

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

Re: Make 1 field from 3 fields

Clever_Anjos — Wed, 04 Jun 2014 18:17:53 GMT

These fields are on same table?

Re: Make 1 field from 3 fields

Wed, 04 Jun 2014 18:35:23 GMT

Yes. In the same Excel file, QVD.

Re: Make 1 field from 3 fields

Clever_Anjos — Wed, 04 Jun 2014 18:39:37 GMT

All that fields are filled?

For example:

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

Re: Make 1 field from 3 fields

maxgro — Wed, 04 Jun 2014 18:50:18 GMT

see attachment

Re: Make 1 field from 3 fields

Wed, 04 Jun 2014 18:50:24 GMT

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

Re: Make 1 field from 3 fields

Wed, 04 Jun 2014 19:28:13 GMT

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

MarcoWedel — Wed, 04 Jun 2014 19:47:58 GMT

Test data for script design

Re: Make 1 field from 3 fields

maxgro — Wed, 04 Jun 2014 19:53:01 GMT

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

Re: Re: Make 1 field from 3 fields

maxgro — Wed, 04 Jun 2014 19:54:47 GMT

script

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

user interface

Re: Make 1 field from 3 fields

marthacano01 — Wed, 04 Jun 2014 20:00:35 GMT

I hope it helps you:

NewTable:

LOAD OTR as [Assigned Pricers]

FROM

(ooxml, embedded labels, table is Sheet1);

LOAD ISO as [Assigned Pricers]

FROM

(ooxml, embedded labels, table is Sheet1);

LOAD IMDL as [Assigned Pricers]

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Re: Make 1 field from 3 fields

MarcoWedel — Wed, 04 Jun 2014 20:03:18 GMT

Hi,

one solution could be:

table1:
LOAD *
FROM [http://community.qlik.com/servlet/JiveServlet/download/542100-109608/QlikCommunity_Thread_120861.txt] (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Left Join (table1)

LOAD Distinct
  OTR,
  ISO,
  IMDL,
  AutoNumberHash128(OTR,ISO,IMDL) as %APID
Resident table1;

tabAP:
CrossTable([Assigned Pricers source], [Assigned Pricers])
LOAD Distinct
  %APID,
  OTR,
  ISO,
  IMDL
Resident table1;

hope this helps

regards

Marco

Re: Make 1 field from 3 fields

Wed, 04 Jun 2014 20:04:49 GMT

Thank you. I’ll try it.

Re: Make 1 field from 3 fields

Wed, 04 Jun 2014 20:06:01 GMT

Thank you. I’ll check it out, but I will need to modify it.

Re: Make 1 field from 3 fields

Wed, 04 Jun 2014 20:09:01 GMT

Thank you. I’ll check it out.

Thank you everyone for all the suggestions. I will be working with them to get my solution.

I appreciate your time.

Paul