String Manipulation

[#1]

Hi Folks,

I am new to QlikView. I stuck at one requirement where I need your help.

I have string like this "ART/01A/CMB/aca/112" I want to extract the 1st String value 'ART' . This string value can be at any position wherever we found 1st occurrence pick up that string. It's always be between '/'. String can be in upper or lower cases.

examples

Field                          Required

100A/aug/AK/102           aug

121/119w/ALA/qc           ALA

MA/100B                       MA

101/110a/99/apa/KJ        apa

119/AJ/BK/001A             AJ

Hope I explained well

[swuehl · Accepted solution]

Maybe something along these lines:

LOAD ID, Only(Field) as Field, Only(Required) as Required, FirstValue(FieldPart) as Result
WHERE Flag
GROUP BY ID;
LOAD *, not isnum (Purgechar(Lower(FieldPart),'abcdefghijklmnopqrstuvwxyz')) as Flag;
LOAD *, Subfield(Field,'/') as FieldPart;
LOAD *, recno() as ID INLINE [
Field,                    Required
100A/aug/AK/102,          aug
121/119w/ALA/qc,          ALA
MA/100B,                  MA
101/110a/99/apa/KJ,        apa
119/AJ/BK/001A,            AJ
];

[sunny_talwar]

Another possibility:

Table:

LOAD (IsNum(KeepChar(Required_Calculated, '0123456789')) + 1) * RowNo() as Flag,

  *;

LOAD Field,

  RecNo() as Key,

  SubField(Field, '/') as Required_Calculated;

LOAD * Inline [

Field, Required

100A/aug/AK/102, aug

121/119w/ALA/qc, ALA

MA/100B, MA

101/110a/99/apa/KJ, apa

119/AJ/BK/001A, AJ

];

Right Join (Table)

LOAD Key,

  Min(Flag) as Flag

Resident Table

Where Flag <> 0

Group By Key;

[#4]

thank you guys.