Subfield formula to split a cell value into multiple records

sadiaasghar — Sat, 16 Nov 2024 17:48:34 GMT

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

Re: Subfield formula to split a cell value into multiple records

Kushal_Chawda — Sat, 21 Nov 2020 00:46:41 GMT

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

Re: Subfield formula to split a cell value into multiple records

Lani8973 — Sat, 21 Nov 2020 06:57:41 GMT

Thank you so much for the solution

Re: Subfield formula to split a cell value into multiple records

Gracielan — Mon, 23 Nov 2020 04:34:00 GMT

Thanks for the information, keep sharing this type of info

topic Re: Subfield formula to split a cell value into multiple records in QlikView

Subfield formula to split a cell value into multiple records

Re: Subfield formula to split a cell value into multiple records

Re: Subfield formula to split a cell value into multiple records

Re: Subfield formula to split a cell value into multiple records