topic Re: Remove last letters in QlikView

Remove last letters

mshailaja — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Remove last letters

Anil_Babu_Samineni — Wed, 19 Apr 2017 04:28:18 GMT

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

Re: Remove last letters

prma7799 — Wed, 19 Apr 2017 04:40:36 GMT

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;

Re: Remove last letters

mshailaja — Wed, 19 Apr 2017 04:49:36 GMT

(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

Re: Remove last letters

prma7799 — Wed, 19 Apr 2017 04:53:09 GMT

I am getting right output ...

Re: Remove last letters

Anil_Babu_Samineni — Wed, 19 Apr 2017 05:01:17 GMT

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

];

Re: Remove last letters

rahulpawarb — Wed, 19 Apr 2017 05:09:30 GMT

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

Re: Remove last letters

avinashelite — Wed, 19 Apr 2017 05:23:03 GMT

try like this

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

Re: Remove last letters

Siva_Sankar — Wed, 19 Apr 2017 05:41:34 GMT

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

];

Re: Remove last letters

mshailaja — Wed, 19 Apr 2017 06:05:50 GMT

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

Re: Remove last letters

jonathandienst — Wed, 19 Apr 2017 06:25:41 GMT

To remove the last subfield regardless of the number of hyphens, use

=Replace(Field, '-' & SubField(Field, '-', -1), '')

(Replace Field with the correct fieldname)

Re: Remove last letters

Aurelien_Martinez — Wed, 19 Apr 2017 09:58:09 GMT

=Left(Field, Index(Field, '-', -1)-1)

Re: Remove last letters

swuehl — Wed, 19 Apr 2017 10:28:45 GMT

Or maybe something like

MAP:

MAPPING LOAD * INLINE [

F1,F2

-CONT,

-DEV,

-PRD,

-UAT,

];

LOAD *, Trim(MapSubString('MAP',Server)) as ServerNew INLINE [

Server
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

];

But it's really depending on the possible values. Above will only work if you need to remove the mapping table values from your server field field values (and they can't appear as valid part of your server name that you want to keep).

Re: Remove last letters

mshailaja — Thu, 20 Apr 2017 06:30:48 GMT

Hi ,

Thnks for all replies,

I have applied this logic and its working fie for me now