topic Re: move the multiple string values from one cell in excel to the next individual rows. in App Development

Re: move the multiple string values from one cell in excel to the next individual rows.

appi_priyakarna — Wed, 09 Oct 2024 18:01:24 GMT

there's a similar file wherein I need help to have the respective filetypes of the column 'Supporting Evidence_H' and 'Supporting SOX Level Evidence' column from the excel, highlighted in yellow. So, if the first column has 5 filetypes and the second column has three file types as a wrapped text. It should generate only 5 individual filetypes for the first column and corresponding three individual filetypes for the second column. But when using the below script, the filetypes from the first and second column are repeating itself making it a count of 15 rows.

Historical_SOX_Campaigns1:
LOAD
*,
'.' & SubField("Supporting Evidence_H",'.',-1) as FileType_H,
'.' & SubField("Supporting SOX Level Evidence_H",'.',-1) as FileType_H1;

LOAD
"Questionnaire ID_H",
"Business Unit Function/Region_H",
"Business Unit Name_H",
"Assessment Status_H",
Target_H,
"Control Question Name_H",
"SOX Priority_H",
Assessor_H,
Reviewer_H,
"SOX Response_H",
"SOX Process Name_H",
"SOX Sub-Process Category_H",
"SOX Tier_H",
"Action Item_H",
"Assessor Due Date_H",
"Assessor Status_H",
"Assessor Submit Date_H",
"Business Unit Division_H",
"Campaign Name_H",
"Campaign Status_H",
"Campaign Type_H",
"Control Activity ID_H",
"Control Background Information_H",
"Control Objective_H",
"Control Question Text_H",
"Company Level Control_H",
"Assessor Comments on Functioning of Control_H",
"Reviewer's Conclusion on Functioning of Control_H",
"Deficiency Review Status_H",
"Description of Local Process and Comments_H",
"Estimated Financial Risk_H",
"Financial Risk Description_H",
"Hard Close?_H",
"ICD Analysis_H",
"ICD Assignment_H",
"ICD Comments_H",
"ICD Rating_H",
"ICD Status_H",
"ICD Submit Date_H",
"Inherited Permissions_H",
"Is there a test template sample size?_H",
"Last Updated_H",
"Link to Testing Template_H",
"Manager's SOX Tier_H",
"Number Compliant in Sample Size_H",
Period_H,
"Policy Reference_H",
"Potential Exposure_H",
"Current Quarter_H",
Quarter_H,
"Reviewer Due Date_H",
"Reviewer Status_H",
"Reviewer Submit Date_H",
"Reviewer Timeliness_H",
"Root Cause_H",
"Assessor Root Cause_H",
"Sample Size_H",
"SOX Response Type_H",
subfield("Supporting Evidence_H", chr(10)) as "Supporting Evidence_H",
"Target Text String_H",
"Tracking ID_H",
Year_H,
"Current Year_H",
// "Supporting SOX Level Evidence_H",
subfield("Supporting SOX Level Evidence_H",chr(10)) as "Supporting SOX Level Evidence_H",
"Submission Status_H",
"Residual Score",
"Remediation Score_H",
"Record Status_H",
"Questions Scored_H",
"Quantitative Summary_H",
"Progress Status_H",
Progress_H,
"Open Findings_H",
Notification_H,
"Maximum Score_H",
"Manager's SOX Priority_H",
"Manager's Assessment Status_H",
"Inherent Score_H",
Incorrect_H,
"Findings Generation Status_H",
Findings_H,
ERM_H,
Current?_H,
"Creation Date_H",
"Created Date_H",
"Created By_H",
Correct_H,
"Assessor Timeliness_H",
"Archived SOX Process Name_H",
"All Findings_H",
"% Correct_H",
"% Compliant_H"
FROM [lib://Data:DataFiles/Data.qvd]
(qvd);

Re: move the multiple string values from one cell in excel to the next individual rows.

MarcoWedel — Sun, 20 Oct 2024 20:17:40 GMT

Hi,
maybe one solution could be:

Historical_SOX_Campaigns1:
LOAD *,
'.' & SubField([Supporting Evidence_H],'.',-1) as FileType_H,
'.' & SubField([Supporting SOX Level Evidence_H],'.',-1) as FileType_H1;
LOAD [Questionnaire ID],
[Supporting Evidence],
SubField([Supporting Evidence], Chr(10),IterNo()) as [Supporting Evidence_H],
[Tracking ID],
[Supporting SOX Level Evidence],
SubField([Supporting SOX Level Evidence],Chr(10),IterNo()) as [Supporting SOX Level Evidence_H]
FROM [Data.xlsx] (ooxml, embedded labels, table is [Raw Data])
While IterNo()<=RangeMax(SubStringCount([Supporting Evidence],Chr(10)),SubStringCount([Supporting SOX Level Evidence],Chr(10)));

Re: move the multiple string values from one cell in excel to the next individual rows.

appi_priyakarna — Mon, 21 Oct 2024 17:40:58 GMT

Hi @MarcoWedel I tried using the above script but from the field Supporting Evidence_H I see only 4 filetypes whereas that particular id SOX-SA-177528 has 5 file types. Can all the file types be added against this id from all the columns that has file types?

Re: move the multiple string values from one cell in excel to the next individual rows.

MarcoWedel — Sun, 27 Oct 2024 19:38:42 GMT

your sample file does not include ID SOX-SA-177528. Can you post an updated version including this ID and the expected result? thanks

Re: move the multiple string values from one cell in excel to the next individual rows.

appi_priyakarna — Mon, 28 Oct 2024 05:59:08 GMT

Hi @MarcoWedel I have highlighted the record in yellow in the Raw Data and Desired Output tab.

Re: move the multiple string values from one cell in excel to the next individual rows.

Kushal_Chawda — Mon, 28 Oct 2024 10:34:43 GMT

@appi_priyakarna Again we don't see any data in your excel.