Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
@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,';');
Thank you so much for the solution
Thanks for the information, keep sharing this type of info