Hi
I have currently have input in this format , Have more than 10K + rows , Just showing an example
Example 1 Input :
([PS Customer Lifecycle].[none:project_number:nk] / ([PS Customer Lifecycle].[none:project_name:nk] / ([PS Customer Lifecycle].[none:wbs_user_status:nk] / ([PS Customer Lifecycle].[none:proj_program_delivery_manager_name:nk]
Expected Result :
[PS Customer Lifecycle].[project],[PS Customer Lifecycle].[project_name],[PS Customer Lifecycle].[wbs_user_status],[PS Customer Lifecycle].[proj_program_delivery_manager_name]
Example 2 Input
([greenplum.41719.542383587963].[sum:lead_count:qk] + ([greenplum.41719.542383587963].[sum:opp_count:qk] + ([greenplum.41719.542383587963].[sum:book_num:qk] + [greenplum.41719.542383587963].[sum:total_booked:qk])))
Expected Output :
[greenplum.41719.542383587963].[Sum(lead_count)],[greenplum.41719.542383587963].[Sum(opp_count)],[greenplum.41719.542383587963].[Sum(book_num)],[greenplum.41719.542383587963].[sum(total_booked)]
Let's take an example ,I basically need to clean the stuff after period which is
[none:project:nk]
,
Input:
[PS Customer Lifecycle].[none:project_number:nk]
Output:
if we have none , we just take the value after the first colon , However if we have anything other than none we take that into consideration.
[PS Customer Lifecycle].[project]
I have achieved the output for one variable using Split , But I have multiple variables separated by ( /, + )
Any Help is appreciated on How to dynamically have one formula to take both the scenario
Thanks
Karthik
Solved! Go to Solution.
Here might be some help:
([\x5b].*[\x5d][.])([\x5b]none:)(.+):(.*)
used within a regex_replace, it can provide you with 4 groupings:
To construct your desired (adjusted) output:
IF Regex_Match([FIELD],"([\x5b].*[\x5d])[.]([\x5b]none:)(.+):(.*)") THEN Regex_Replace([FIELD],"([\x5b].*[\x5d])[.]([\x5b]none:)(.+):(.*)","$1.[$3]") ELSE [FIELD] ENDIF
This will create:
Does this help you?
Thanks,
Mark
Thanks for your input. But it does not solve when we have more than one values. As shown below
([PS Customer Lifecycle].[none:project_number:nk] / ([PS Customer Lifecycle].[none:project_name:nk] / ([PS Customer Lifecycle].[none:wbs_user_status:nk] / ([PS Customer Lifecycle].[none:proj_program_delivery_manager_name:nk]
Expected Result :
[PS Customer Lifecycle].[project],[PS Customer Lifecycle].[project_name],[PS Customer Lifecycle].[wbs_user_status],[PS Customer Lifecycle].[proj_program_delivery_manager_name]
However if we have anything other than none , we need to take that in to consideration :
([greenplum.41719.542383587963].[sum:lead_count:qk] + ([greenplum.41719.542383587963].[sum:opp_count:qk] + ([greenplum.41719.542383587963].[sum:book_num:qk] + [greenplum.41719.542383587963].[sum:total_booked:qk])))
Expected Output :
[greenplum.41719.542383587963].[Sum(lead_count)],[greenplum.41719.542383587963].[Sum(opp_count)],[greenplum.41719.542383587963].[Sum(book_num)],[greenplum.41719.542383587963].[sum(total_booked)]
All the above can come in a single scenario , so I am looking for a formula to be dynamic enough to handle all the scenarios,
The separation can be a (/,+) as shown in example 1 ( the values are separeted by / and ine example 2 where as the values are seprated by + value (i.e., value is other than none).
Thanks for your help , let me know if i am not clear with my ask.
Thanks
Karthik
Thanks @MarqueeCrew. I definetly understand how to deal with different values (like none and sum) , However still i did not understand how can i get it to solve when we have more than 1 values as shown in the above example separated by (/,+) . Any thoughts on it would be really helpful.
Thanks for your time.
Thanks
Karthik
([PS Customer Lifecycle].[none:project_number:nk] / ([PS Customer Lifecycle].[none:project_name:nk] / ([PS Customer Lifecycle].[none:wbs_user_status:nk] / ([PS Customer Lifecycle].[none:proj_program_delivery_manager_name:nk]
looks like you can check for the / in the field and then parse it to include many groups.
The replacement would be like:
$1.$2 , $3.$4
I am not near a computer to help further today.
here is some more help.
Thanks,
Mark
Thanks @MarqueeCrew
It works like charm.
Found below website useful for trying regex functions
https://regex101.com/r/xF4jF3/10
Thanks
Karthik