topic Re: String Manipulation in QlikView

String Manipulation

Sat, 14 May 2016 11:00:38 GMT

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

Re: String Manipulation

swuehl — Sat, 14 May 2016 11:45:41 GMT

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
];

Re: String Manipulation

sunny_talwar — Sun, 15 May 2016 01:40:12 GMT

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;

Re: String Manipulation

Sun, 15 May 2016 08:02:18 GMT

thank you guys.