Wild Match

[jpjust]

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

[MarcoWedel]

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.

[jpjust]

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?

[MarcoWedel]

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?

[jpjust]

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

[MarcoWedel]

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

[jpjust]

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.