First 9 characters of a field

[#1]

Hi,

Simple question today.

I have a field where I only need the first 9 characters of the field. The field will have alpha, numeric and "-" in it.

I.E. the data looks like the table below, I only need the first 9 characters to be loaded, irrespective of what is in the field - even if there is not 9 characters.

TR-A-APMT

TR-A-BANK

TR-A-CMPS

TR-A-CMPS

TR-A-CMPS

TR-A-CURA

TR-A-CURA-1

TR-A-EDEX

TR-A-ESEC

TR-A-ESSI

TR-A-FCBS-2

TR-A-FCBS-3

TR-A-FCBS-4

TR-A-FCBS-2

TR-A-FCBS-1

TR-A-FCBS

TR-A-FCBS

TR-A-FCBS

TR-A-FCBS

TR-A-FCBS

TR-A-FCBS

TR-A-FCBS

TR-A-FCBS

TR-A-FORM

TR-A-GLHD

TR-A-GRPV2

TR-A-ISLP-1

TR-A-KFTA

TR-A-OSTF

TR-A-PM

TR-A-PRFM

TR-A-prfm

TR-A-QLIK

TR-A-QLIK

TR-A-QLIK

TR-A-QLIK

TR-A-QUOT

TR-A-RLNK

TR-A-RLNK

TR-A-RLNK

TR-A-RLNK

TR-A-RLNK

TR-A-RPDM-1

TR-A-SMRT

TR-A-SMRT

TR-A-SMRT

TR-A-SMRT

TR-A-SMRT

TR-A-TCFM

TR-A-TFSA

TR-A-UNIQ

TR-S-BANK

TR-S-BANK-5

TR-S-BANK 3

TR-S-BANK4

TR-S-MBTS

TR-S-MBTS

TR-S-MBTS

TR-S-MBTS

TR-S-MBTS

TR-S-MBTS

TR-S-MBTS

[sunny_talwar · Accepted solution]

Try this:

Left(FieldName, 9) as NewFieldName

[sunny_talwar]

If you need 9 without the '-' hyphen, then try this:

Left(PurgeChar(FieldName, '-'), 9) as NewFieldName

[MayilVahanan]

Hi

Try like this

Left(FieldName, 9) as FieldName

[ashwanin]

Hi,

left(field,9) as Mod_field   will solve your query.

[sasiparupudi1]

left(value &repeat(' ',9),9)

[sasiparupudi1]

LOAD value,len(value) as x,newValue,len(newValue) as y;

load value,left(value &repeat(' ',9),9) as newValue inline

[

value

TR-A-APMT

TR-A-BANK

TR-A-CMPS

TR-A-CMPS

TR-A-CMPS

TR-A-CURA

TR-A-CURA-1

TR-A-EDEX

TR-A-ESEC

TR-A-ESSI

TR-A-FCBS-2

TR-A-FCBS-3

TR-A-FCBS-4

TR-A-FCBS-2

TR-A-FCBS-1

TR-A-FCBS

TR-A-FCBS

TR-A-FCBS

TR-A-FCBS

TR-A-FCBS

TR-A-FCBS

TR-A-FCBS

TR-A-FCBS

TR-A-FORM

TR-A-GLHD

TR-A-GRPV2

TR-A-ISLP-1

TR-A-KFTA

TR-A-OSTF

TR-A-PM

TR-A-PRFM

TR-A-prfm

TR-A-QLIK

TR-A-QLIK

TR-A-QLIK

TR-A-QLIK

TR-A-QUOT

TR-A-RLNK

TR-A-RLNK

TR-A-RLNK

TR-A-RLNK

TR-A-RLNK

TR-A-RPDM-1

TR-A-SMRT

TR-A-SMRT

TR-A-SMRT

TR-A-SMRT

TR-A-SMRT

TR-A-TCFM

TR-A-TFSA

TR-A-UNIQ

TR-S-BANK

TR-S-BANK-5

TR-S-BANK 3

TR-S-BANK4

TR-S-MBTS

TR-S-MBTS

TR-S-MBTS

TR-S-MBTS

TR-S-MBTS

TR-S-MBTS

TR-S-MBTS

];

[qlikviewwizard]

Hi,

Please use this script.

Table:

LOAD * ,

Left(Field, 9) as NewFieldName1,

Left(PurgeChar(Field, '-'), 9) as NewFieldName2;

LOAD * INLINE [Field

TR-A-APMT

TR-A-BANK

TR-A-CMPS

TR-A-CMPS

TR-A-CMPS

TR-A-CURA

TR-A-CURA-1

TR-A-EDEX];