Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
These fields are on same table?
Yes. In the same Excel file, QVD.
All that fields are filled?
For example:
If OTR is 'doug' , ISO is wmpty or has a value?
see attachment
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
I see the results are the way I need them, but I can’t see the script you created to get to that point.
Test data for script design
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
script
see attachment for script (I already posted it but is currently being moderated so you can't see it)
user interface