Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
All,
I have an expression as below.
IF(Wildmatch([name_u2],'*(1)*'),Replace([name_u2],'(1)',''),
IF(Wildmatch([name_u2],'*(2)*'),Replace([name_u2],'(2)',''),
IF(Wildmatch([name_u2],'*test*'),Replace([name_u2],'test',''),
IF(Wildmatch([name_u2],'*(3)*'),Replace([name_u2],'(3)',''),[name_u2]))))as PublishedAppName,
As you can see from the above expression, if name_u2 equals NewApptest then the PublishedAppName should be NewApp
I cannot presume always it will be test or (1) etc., How can I write an expression even if NewApp-ABC then PublishedAppName is NewApp? Some examples below.
NewApp-ABC - > NewApp
FirstApp(1) -> FirstApp
SecondAppCDE -> SecondApp
Is it possible?
Thanks
If you can specify a rule that unambiguously identifies the substring you want to purge and that always applies to your data, then it's possible.
Hi Marco -The rule would be that, the app name will be consistent for few characters at least like for NewApp the app name could be renamed as NewAppProd or NewApp-Prod or NewApp-Test but NewApp won't change drastically as AppNew. So at least first few characters of the app name remains the same.
Any possibility of an intelligent expression in the above case?
What do you mean by "remains the same"?
Over time? In comparison to other occurrences of the app name?
Can you post some more examples of different app names and your expected result?
Sorry for the confusion.
Here are some examples
Appname Expectedoutput
Appname-new Appname
Appnametest Appname
AppnameProd Appname
Test(1) Test
TestABC Test
ReportNEW Report
Report(2) Report
If there could be some app names starting with the same substring, I think the expected result still would not be well defined.
What you could do however is to create some sort of hierarchy of common app name start strings, e.g. like this:
Maybe one of these AppNameX fields could be used to identify your different apps or at least might help you to derive your expected output based on some additional rules.
tabAppNames:
LOAD * Inline [
Appname
Appname-new
Appnametest
AppnameProd
Test(1)
TestABC
ReportNEW
Report(2)
Application<1>
Application<2>
Application-dev
Application-test
Application-prod
TestApp-new
TestApp-old
TestApp-evenolder
ReportingApp 1
ReportingApp 2
ReportingApp 3
SomeLonelyApp
];
tabAppLeftTemp:
LOAD Distinct
Appname,
Left(Appname,IterNo()) as AppLeft,
IterNo() as AppLen
Resident tabAppNames
While IterNo() <= Len(Appname);
tabAppLeftCount:
LOAD AppLeft,
AppLen,
Count(DISTINCT Appname) as AppNameCount
Resident tabAppLeftTemp
Group By AppLeft,AppLen;
Left Join (tabAppLeftCount)
LOAD AppLeft,
Max(AppNameCount) as AppNameMaxNextCount
Group By AppLeft;
LOAD Left(AppLeft,AppLen-1) as AppLeft,
AppNameCount
Resident tabAppLeftCount;
Right Join (tabAppLeftCount)
LOAD AppLeft
Resident tabAppLeftCount
Where AppNameCount <> AppNameMaxNextCount;
Right Join (tabAppLeftTemp)
LOAD AppLeft
Resident tabAppLeftCount;
tabAppLeft:
LOAD Appname,
AppLeft,
AppLen,
AutoNumber(AppLen,Appname) as AppNamePathLevel
Resident tabAppLeftTemp
Order By Appname,AppLen;
Join (tabAppLeft)
LOAD Max(AppNamePathLevel) as MaxAppNamePathLevelTotal
Resident tabAppLeft;
Join (tabAppLeft)
LOAD Appname,
Max(AppNamePathLevel) as MaxAppNamePathLevel
Resident tabAppLeft
Group By Appname;
tabAppNamesTemp:
Generic
LOAD Appname,
'AppName'&(AppNamePathLevel+IterNo()-1),
AppLeft
Resident tabAppLeft
While IterNo()=1 or (AppNamePathLevel=MaxAppNamePathLevel and IterNo()<=MaxAppNamePathLevelTotal-MaxAppNamePathLevel+1);
FOR i = NoOfTables()-1 to 0 step -1
LET vTable=TableName($(i));
IF '$(vTable)' like 'tabAppNamesTemp.*' then
Join (tabAppNames) LOAD * Resident [$(vTable)];
DROP Table [$(vTable)];
ENDIF
NEXT i
DROP Tables tabAppLeftTemp, tabAppLeft, tabAppLeftCount;
hope this helps
Marco
Thank you so much Marco!! Really appreciate taking your time here.
I will give it a try and let you know. Like you mentioned, the end result is still not going to be perfect.