Remove last letters

[mshailaja]

Hi

i have the table in which one filed values are like that

Server is the Fieldname and the values are

Server1-AP-DEV

Server2-AP-QA

Server3-AP-PRD

Server4-AP-DEV

Server5-AP-QA

Server2-AP-PRD

I need to be the fileds as like below

Server1-AP

Server2-AP

server3-AP

I applied the logic as like below for DEV

But i need for DEV,QA,PRD

Can any one please look into it

if(right(Server,4)='-DEV',Replace(Server,Right(Server,4),''),Server) as server1

[Anil_Babu_Samineni]

From this data what are you exactly expecting as o/p

Server1-AP-DEV

Server2-AP-QA

Server3-AP-PRD

Server4-AP-DEV

Server5-AP-QA

Server2-AP-PRD

O/P is this?

---

Server1-AP

Server2-AP

server3-AP

Server4-AP

Server5-AP

[prma7799]

Please Try this

NEW:

LOAD * Inline

[

Test

Server1-AP-DEV,

Server2-AP-QA,

Server3-AP-PRD,

Server4-AP-DEV,

Server5-AP-QA,

Server2-AP-PRD

];

NEW2:

LOAD

(if( not SubField (Test,'-',3) = 0 , left(Test,10)))  as NewTest

Resident NEW ;

drop Table NEW;

[mshailaja]

Hi

(if( not SubField (Test,'-',3) = 0 , left(Test,10)))  as NewTest

is not working

@ Anil

Yes i want like this

I have thousands of values coming like that from source

[prma7799]

I am getting right output ...

[Anil_Babu_Samineni]

Cool, If you have same structure follows then you will go like below

LOAD SubField(Server,'-', 1) & '-' & SubField(Server,'-', 2) as Server  Inline [

Server

Server1-AP-DEV

Server2-AP-QA

Server3-AP-PRD

Server4-AP-DEV

Server5-AP-QA

Server2-AP-PRD

];

[rahulpawarb]

Hello Sailaja,

Trust that you are doing good!

Please refer below sample script to get desired result:

ServerList:
LOAD Server,
     SubField(Server, '-', 1) & '-' & SubField(Server, '-', 2) AS Text;
LOAD * INLINE [
Server
Server1-AP-DEV,
Server2-AP-QA,
Server3-AP-PRD,
Server4-AP-DEV,
Server5-AP-QA,
Server2-AP-PRD
];

Also refer the sample application attached herewith.

Regards!

Rahul

[avinashelite]

try like this

SubField(Server,'-', 1) & '-' & SubField(Server,'-', 2) as Server

[Siva_Sankar]

How about the below one???

Load * ,

Left(Server,10) as server1  

inline

[

Server

Server1-AP-DEV

Server2-AP-QA

Server3-AP-PRD

Server4-AP-DEV

Server5-AP-QA

Server2-AP-PRD

];

[mshailaja]

Sorry all,

I am giving field values

HSU-SGD-ICPM-MANAGement

HSU-SGD-ICPM-MANAGement- CONT

HSU-SGD-ICPM-MANAGement- DEV

HSU-SGD-ICPM-MANAGement- PRD

HKU-SPD-ICPMPI-MANAGement

HKU-SPD-ICPMPI-MANAGement-DEV

HKU-SPD-ICPMPI-MANAGement-UAT

Like this i have thousands of fileds coming form the datasource, i cannot hardcode them as there are large number

I need to show as  for example ( beacuse  based on this HSU-SGD-ICPM-MANAGement) i am making join with other table , if i am using HSU-SGD-ICPM-MANAGement- CONT, join is not happening as it doesnt match)

HSU-SGD-ICPM-MANAGement- CONT  as  HSU-SGD-ICPM-MANAGement

HSU-SGD-ICPM-MANAGement- DEV  as  HSU-SGD-ICPM-MANAGement

I cannot hardcode them as there are thousands of fields values like above with different names ending with -CONT,-DEV,-UAT