Subfield formula to split a cell value into multiple records

[sadiaasghar]

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 

Actual Records:

ows_Activity_Status

ows_Actual_End_Time

ows_Actual_Start_Time

ows_Completion_Percentage

ows_Fiscal_Year

ows_Measure

ows_Planned_End_x0020_Time

ows_Planned_Start_Time

ows_Project

ows_Sub_Activities

ows_Sub_Activities_Weightage

ows_Sub_Project

ows_Submission_Date

Complete; Complete;

2020-11-18; 2020-11-18;

2020-11-17; 2020-11-17;

100; 100;

2021

HEALTH, SAFETY & ENVIRONMENT

2020-11-16; 2020-11-18;

2020-11-16; 2020-11-17;

Major Oil Spills < 200 litres

1st; ​2ND;

0.778; ;

Major Oil Spills < 200 litres

2020-11-16 00:00:00

Complete; Complete; Planned; Planned; Planned;

2020-07-31; 2020-08-31; 2020-09-30; 2020-10-31; 2020-11-30;

2020-07-01; 2020-08-01; 2020-09-01; 2020-10-01; 2020-11-01;

100; 100; 0; 0; 0;

2021

BUSINESS DRIVERS

2020-07-31; 2020-08-31; 2020-09-30; 2020-10-31; 2020-11-30;

2020-07-01; 2020-08-01; 2020-09-01; 2020-10-01; 2020-11-01;

NFR

​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		NFR-Revamped C-Store	2020-11-17 00:00:01
Planned	2020-09-30	2020-09-01	0	2021	BUSINESS DRIVERS	2020-09-30	2020-09-01	NFR	​Tender Floating		NFR-Revamped C-Store	2020-11-17 00:00:01
Planned	2020-10-31	2020-10-01	0	2021	BUSINESS DRIVERS	2020-10-31	2020-10-01	NFR	Bids Evaluation​		NFR-Revamped C-Store	2020-11-17 00:00:01
Planned	2020-11-30	2020-11-01	0	2021	BUSINESS DRIVERS	2020-11-30	2020-11-01	NFR	Award of Contract​		NFR-Revamped C-Store	2020-11-17 00:00:01

[Kushal_Chawda · Accepted solution]

@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,';');

[Lani8973]

Thank you so much for the solution 

[Gracielan]

Thanks for the information, keep sharing this type of info