Duplicate/Modify Row if meet criteria

[ssahir]

Hi,

I need help create script to duplicate/modify existing row if meet certain criteria.

Existing Data
linked_resource	template_name	column	field_id	field_value
ABC	metadata_tag	app_category	attribute_name	App Category
ABC	metadata_tag	app_name	attribute_name	App Name
ABC	xform_logic_tag	app_category	transformation_logics	1234567890
ABC	xform_logic_tag	app_name	transformation_logics	ABCDEFGHIJ
Condition if field_id ='transformation_logics' and len(field_vale)>=4
1. Need tp truncate the field_value (with maximum 4 character only)
2. Duplicate the affected row change field_id with transformation_logics_2 and field_value capture the next 4 character only 3. Continue this process until all character for initial field_value is covered
Expected Result
linked_resource	template_name	column	field_id	field_value
ABC	metadata_tag	app_category	attribute_name	App Category
ABC	metadata_tag	app_name	attribute_name	App Name
ABC	xform_logic_tag	app_category	transformation_logics	1234
ABC	xform_logic_tag	app_name	transformation_logics	ABCD
ABC	xform_logic_tag	app_category	transformation_logics_2	5678
ABC	xform_logic_tag	app_name	transformation_logics_2	EFGH
ABC	xform_logic_tag	app_category	transformation_logics_3	90
ABC	xform_logic_tag	app_name	transformation_logics_3	IJ

[vinieme12]

temp:
load *,if(wildmatch(field_id,'transformation_logics') and len(field_value)>4,field_value) as transform_field
inline [
linked_resource,template_name,column,field_id,field_value
ABC,metadata_tag,app_category,attribute_name,App Category
ABC,metadata_tag,app_name,attribute_name,App Name
ABC,xform_logic_tag,app_category,transformation_logics,1234567890
ABC,xform_logic_tag,app_name,transformation_logics,ABCDEFGHIJ
];

left join(temp)
load transform_field
,mid(transform_field,if(iterno()-1=0,iterno(),(iterno()-1)*4+1),4) as newValue
While iterno()<=iterations
;
Load
FieldValue('transform_field', iterno()) as transform_field
,ceil(len(FieldValue('transform_field', iterno()))/4) as iterations
AUTOGENERATE 1
While len(FieldValue('transform_field',iterno()));


NoConcatenate
Main:
Load
linked_resource,template_name,column,field_id,if(len(newValue),newValue,field_value) as field_value
Resident temp;
drop table temp;

exit Script;

 

[vinieme12]

kindly close the thread by marking a response as solution