Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
kindly close the thread by marking a response as solution