Subfield formula to split a cell value into multiple records
Hello guys, I need help with Sub Field formula.
I have a table with multiple columns. So, we can have 2 or more values in one cell with ; (semi-colon) as a separator. We have 8 columns of this type. So users can input even 10 or 20 values in one cell. I am using sub field approach but it creates multiple records like in my example i have 2 records. First record can be split into two rows and 2nd can be into 5 rows so we should have a total of 7 rows. But by using sub field approach it's creating more than thousand records. Please help me with this. @sunny_talwar@Kushal_Chawda
Site Identification & Negotiation with dealers for agreement signing; Specification Revision and Tender Document Finalization; Tender Floating ; Bids Evaluation; Award of Contract;
; ; ; ; ;
NFR-Revamped C-Store
2020-11-17 00:00:00
Desired Output:
ows_Activity_x0020_Status
ows_Actual_x0020_End_x0020_Time
ows_Actual_x0020_Start_x0020_Time
ows_Completion_x0020_Percentage
ows_Fiscal_x0020_Year
ows_Measure
ows_Planned_x0020_End_x0020_Time
ows_Planned_x0020_Start_x0020_Time
ows_Project
ows_Sub_x0020_Activities
ows_Sub_x0020_Activities_x0020_Weightage
ows_Sub_x0020_Project
ows_Submission_x0020_Date
Complete
2020-11-18
2020-11-17
100
2021
HEALTH, SAFETY & ENVIRONMENT
2020-11-16
2020-11-16
Major Oil Spills < 200 litres
1st
0.778
Major Oil Spills < 200 litres
2020-11-16 00:00:00
Complete
2020-11-18
2020-11-17
100
2021
HEALTH, SAFETY & ENVIRONMENT
2020-11-18
2020-11-17
Major Oil Spills < 200 litres
2ND
Major Oil Spills < 200 litres
2020-11-16 00:00:00
Complete
2020-07-31
2020-07-01
100
2021
BUSINESS DRIVERS
2020-07-31
2020-07-01
NFR
Site Identification & Negotiation with dealers for agreement signing
NFR-Revamped C-Store
2020-11-17 00:00:01
Complete
2020-08-31
2020-08-01
100
2021
BUSINESS DRIVERS
2020-08-31
2020-08-01
NFR
Specification Revision and Tender Document Finalization
@sadiaasghar assuming that you have ";" in end of the string as well like "Complete; Complete;". If you just have string like "Complete; Complete" then use substringcount()+1 in while condition
Data:
LOAD SubField(ows_Activity_Status,';',IterNo()) as ows_Activity_Status,
SubField(ows_Actual_End_Time,';',IterNo()) as ows_Actual_End_Time,
SubField(ows_Actual_Start_Time,';',IterNo()) as ows_Actual_Start_Time,
SubField(ows_Completion_Percentage,';',IterNo()) as ows_Completion_Percentage,
SubField(ows_Planned_End_x0020_Time,';',IterNo()) as ows_Planned_End_x0020_Time,
SubField(ows_Planned_Start_Time,';',IterNo()) as ows_Planned_Start_Time,
SubField(ows_Sub_Activities,';',IterNo()) as ows_Sub_Activities,
SubField(ows_Sub_Activities_Weightage,';',IterNo()) as ows_Sub_Activities_Weightage,
SubField(ows_Submission_Date,';',IterNo()) as ows_Submission_Date,
ows_Fiscal_Year,
ows_Measure,
ows_Project,
ows_Sub_Project
FROM [lib://web]
(html, utf8, embedded labels, table is @1)
while IterNo()<= SubStringCount(ows_Actual_Start_Time,';');
@sadiaasghar assuming that you have ";" in end of the string as well like "Complete; Complete;". If you just have string like "Complete; Complete" then use substringcount()+1 in while condition
Data:
LOAD SubField(ows_Activity_Status,';',IterNo()) as ows_Activity_Status,
SubField(ows_Actual_End_Time,';',IterNo()) as ows_Actual_End_Time,
SubField(ows_Actual_Start_Time,';',IterNo()) as ows_Actual_Start_Time,
SubField(ows_Completion_Percentage,';',IterNo()) as ows_Completion_Percentage,
SubField(ows_Planned_End_x0020_Time,';',IterNo()) as ows_Planned_End_x0020_Time,
SubField(ows_Planned_Start_Time,';',IterNo()) as ows_Planned_Start_Time,
SubField(ows_Sub_Activities,';',IterNo()) as ows_Sub_Activities,
SubField(ows_Sub_Activities_Weightage,';',IterNo()) as ows_Sub_Activities_Weightage,
SubField(ows_Submission_Date,';',IterNo()) as ows_Submission_Date,
ows_Fiscal_Year,
ows_Measure,
ows_Project,
ows_Sub_Project
FROM [lib://web]
(html, utf8, embedded labels, table is @1)
while IterNo()<= SubStringCount(ows_Actual_Start_Time,';');
