Need Help in Qlik

[sona_sa]

Hi,

I have a field EXTRA and Value is like this :

AOB 327, AOB 328, Case Drain 103, Case Drain 107, FLIB 110, FLIB 319

I need only alphabetically character from this field; How I can derive in Qlik.

Like AOB, Case Drain, FLIB.

Thanks

[Oleg_Troyansky]

Hi,

If all values have the same structure, with the textual part and the numeric part separated by a space, then the easiest solution would be to use the Subfield() function:

Subfield(EXTRA, ' ', 1) 

Cheers,

Oleg Troyansky

[priyanka181088]

u can  use purgechar() function

purgechar(EXTRA,' 123456789')

Thanks,

Priyanka

[marksouzacosta]

Hi @sona_sa,

You can also try the Regular Expression features:

Temp:
LOAD
  EXTRA,
  ReplaceRegEx(EXTRA,'\d+','') AS CleanExtra
INLINE [
EXTRA
AOB 327
AOB 328
Case Drain 103
Case Drain 107
FLIB 110
FLIB 319
12736213 TES123TR 319
];

Result:

Source: ReplaceRegEx - Script and chart function | Qlik Cloud Help

Regards,

Mark Costa

[Kaushik2020]

Hi @sona_sa  Please see below option via data load editor. 

Table:
Load * INLINE [
EXTRA
AOB 327
AOB 328
Case Drain 103
Case Drain 107
FLIB 110
FLIB 319 ];

Final:
    LOAD DISTINCT
         Trim(PurgeChar(SubField(EXTRA, ',', IterNo()), '0123456789')) AS ExtraText,
         EXTRA as OldText
RESIDENT Table
WHILE IterNo() <= SubStringCount(EXTRA, ',') + 1;

Drop Table Table;

[howdash · Accepted solution]

You can also use the KeepChar() function, like this:

tableName:
Load EXTRA,
Trim(KeepChar(EXTRA, 'aAbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTuUvVwWxXyYzZ ')) as alphaEXTRA
Inline [
EXTRA
AOB 327
AOB 328
Case Drain 103
Case Drain 107
FLIB 110
FLIB 319
];

This ensures that only letters that you specify will be kept.