Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
rubenacampos
New Contributor

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

Re: Load Script to seperate data

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
New Contributor

Re: Load Script to seperate data

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!

Community Manager
Community Manager

Re: Load Script to seperate data

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

Sue Macaluso
rubenacampos
New Contributor

Re: Load Script to separate data

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