Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 Number | Contract Status | Contract Due Date | Business Sector | Contract Completion Date | Organization | Period of Performance | Evaluation Status | Evaluation Type | Evaluation Due Date | Users |
W9128F19F0028 | CURRENT | 05/13/2020 | Nonsystems | 08/21/2019 | USACE (NWO)Omaha District | 01/15/2019 - 01/14/2020 | Drafted | Interim | 05/13/2020 | Focal Point (DONALD MILLER; name@email.com; XXX-XXX-XXXX) Alternate Focal Point (JEFFREY WYANT; name@email.coml; XXX-XXX-XXXX) |
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!
@rubenacampos Are you using QlikView or Sense? I would like to move this into the correct product forum.
Hello Sue, I'm using Qlik Sense. Thank you.