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: 
rubenacampos
Contributor III
Contributor III

Load Script to seperate data

Is there a script to separate data in one column into separate columns?  Below is an exert of the excel table containing my data. The "Users" column groups all the users into one cell.  I would like to separate the user types (Focal Point, Alternate Focal Point, Contractor Rep, Assessing Official Rep, Assessing Official, and Reviewing Official) into their own columns then list only the names of the users into their respective columns.  Thank you in advance for your help!

 

Order NumberContract StatusContract Due DateBusiness SectorContract Completion DateOrganizationPeriod of PerformanceEvaluation StatusEvaluation TypeEvaluation Due DateUsers
W9128F19F0028CURRENT05/13/2020Nonsystems08/21/2019USACE (NWO)Omaha District01/15/2019 - 01/14/2020DraftedInterim05/13/2020

Focal Point (DONALD MILLER; name@email.com; XXX-XXX-XXXX)

Alternate Focal Point (JEFFREY WYANT; name@email.coml; XXX-XXX-XXXX)
Alternate Focal Point (MELISSA HARLON; name@email.coml; XXX-XXX-XXXX)
Contractor Rep (BILLY CRESS; name@email.coml; XXX-XXX-XXXX)
Contractor Rep (DREW CRUZ; name@email.coml; XXX-XXX-XXXX)
Assessing Official Rep (ANDREW MCPHERSON; name@email.coml; XXX-XXX-XXXX)
Assessing Official (HEATHER DUGGAN; name@email.coml; XXX-XXX-XXXX)
Reviewing Official (CHRISTINE WILSON; name@email.coml; XXX-XXX-XXXX)

 

Labels (1)
4 Replies
Lisa_P
Employee
Employee

I have managed to extract this field out using this script:

Split:
LOAD
Replace(Users, '(', ';' ) as SplitUsers
FROM [lib://Community/Users.xlsx]
(ooxml, embedded labels, table is Sheet1);

SubField:
Load
SubField(SplitUsers, ';' , 1) as UserType,
SubField(SplitUsers, ';' , 2) as UserName,
SubField(SplitUsers, ';' , 3) as UserEmail,
Left(SubField(SplitUsers, ';' , 4),13) as UserPhone
resident Split;
rubenacampos
Contributor III
Contributor III
Author

Lisa,

Thank you.  The script does split the users out but it's not making a link to the Contract ID nor can I set the individual user types as column headers so that my table lists the users horizontally across each record.  Below is the load script, perhaps my load script is wrong:

LOAD
"Contract Number",
"Order Number",
"Contract Number"&"Order Number" as "Contract ID",
"Contract Status",
"Contract Due Date",
"Business Sector",
"Contract Completion Date",
Organization,
"Period of Performance",
"Evaluation Status",
"Evaluation Type",
"Evaluation Due Date",
Users
FROM [lib://AttachedFiles/StatusReport.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Sheet 1]);


Split:
LOAD
Replace(Users, '(', ';' ) as SplitUsers
FROM [lib://AttachedFiles/StatusReport.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Sheet 1]);

SubField:
Load
SubField(SplitUsers, ';' , 1) as UserType,
SubField(SplitUsers, ';' , 2) as UserName,
SubField(SplitUsers, ';' , 3) as UserEmail,
Left(SubField(SplitUsers, ';' , 4),13) as UserPhone
resident Split;

Thanks again for your help!

Sue_Macaluso
Community Manager
Community Manager

@rubenacampos Are you using QlikView or Sense? I would like to move this into the correct product forum.

Sue Macaluso
rubenacampos
Contributor III
Contributor III
Author

Hello Sue, I'm using Qlik Sense.  Thank you.