Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I am working to create two new field based on a id
The ID looks like this:
123-16
234-17
556P
345P
234-15
I would like to create an if statement in the script that separates the P from the -##, but I haven't been able to get it to load. I want the "P" to be named primary, and the -## to be named historical.
if you can help I would appreciate it!
Maybe some simple string functions already are sufficient to separate your field values:
LOAD
if(Right([%ClimateWorksID],1)='P', [%ClimateWorksID]) as [Primary Porfolio ID],
if(Mid([%ClimateWorksID],4,1)='-', [%ClimateWorksID]) as [Historic Porfolio ID];
LOAD * INLINE [
%ClimateWorksID
123-16
234-17
556P
345P
234-15
];
I think I'm close
if(WildMatch([%ClimateWorksID]='###P',1,0), [%ClimateWorksID]) as [Primary Porfolio ID],
Maybe some simple string functions already are sufficient to separate your field values:
LOAD
if(Right([%ClimateWorksID],1)='P', [%ClimateWorksID]) as [Primary Porfolio ID],
if(Mid([%ClimateWorksID],4,1)='-', [%ClimateWorksID]) as [Historic Porfolio ID];
LOAD * INLINE [
%ClimateWorksID
123-16
234-17
556P
345P
234-15
];
Or using index() function
LOAD
if(index([%ClimateWorksID],'P'), [%ClimateWorksID]) as [Primary Porfolio ID],
if(index([%ClimateWorksID],'-'), [%ClimateWorksID]) as [Historic Porfolio ID];
LOAD * INLINE [
%ClimateWorksID
123-16
234-17
556P
345P
234-15
];
Also..
if(WildMatch([%ClimateWorksID],'*P'), [%ClimateWorksID]) as [Primary Porfolio ID],
if([%ClimateWorksID] Like '*-*', [%ClimateWorksID]) as [Historic Porfolio ID];