Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
@Kushal_Chawda Hi Kushal,
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);
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)));
see also: SubField().
hope this helps
Marco
