topic Re: QlikView text manipulation question in QlikView

QlikView text manipulation question

m_monfort — Tue, 11 Sep 2018 14:38:45 GMT

Hi all,

I've been looking at the various text manipulation options in script but cannot seem to find a way forward so need the communities help.

I have a list of data that comes through with headers as follows:

AA10P

AA10C

AA10SPF

AA11.1P

AA11.1C

AA11.1SPF

AA23.2/45P

AA23.2/45C

AA23.2/45SPF

The unique fields above cover only 3 industry codes, AA10, AA11.1 and AA23.2

If these were of a fixed length I could just use a left( ) function but the size varies.

Additionally, I also want to pull out the measure portion which in this example is the P, C and SPF values shown at the end of each string.

How can I do this in the script?

If it helps, there are always 2 letters to start off for each industry (ie there will always be 2 letters like AA followed by a set of values that are just numbers (eg AA10), have decimal points (eg AA11.1) or have a another decimal point and/or separator (eg AA23.2/45)

Please help

Re: QlikView text manipulation question

Frank_Hartmann — Tue, 11 Sep 2018 16:25:20 GMT

try like this:

Load *, if(WildMatch(Test,'*.*')=1,subfield(Test,'.',1)&'.'&left(SubField(Test,'.',2),1),left(Test,4)) as New,
Purgechar(PurgeChar(Test,if(WildMatch(Test,'*.*')=1,subfield(Test,'.',1)&'.'&left(SubField(Test,'.',2),1),left(Test,4))),'/') as New1;


LOAD * INLINE [
    Test
    AA10P
    AA10C
    AA10SPF
    AA11.1P
    AA11.1C
    AA11.1SPF
    AA23.2/45P
    AA23.2/45C
    AA23.2/45SPF
];

Re: QlikView text manipulation question

Anonymous — Tue, 11 Sep 2018 16:37:26 GMT

hi try this,

temp:

LOAD header,

IF(WildMatch(header,'AA10*')=1,'AA10',if(WildMatch(header,'AA11.1*')=1,'AA11.1',if(WildMatch(header,'AA23.2*')=1,'AA23.2',''))) as industrycode

FROM [data.xlsx] (ooxml, embedded labels, table is Sheet4);

left join(temp)

load

header,

PurgeChar(header,industrycode)as separator

Resident temp;

Re: QlikView text manipulation question

vishsaggi — Tue, 11 Sep 2018 18:17:53 GMT

May be try this?

LOAD *,

Left(TextVal, Index(TextVal, KeepChar(PurgeChar(TextVal,'AA'), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'), 1)-1) AS NewFieldValue INLINE [

TextVal

AA10P

AA10C

AA10SPF

AA11.1P

AA11.1C

AA11.1SPF

AA23.2/45P

AA23.2/45C

AA23.2/45SPF

];

Re: QlikView text manipulation question

MarcoWedel — Tue, 11 Sep 2018 20:41:15 GMT

Hi,

another solution might be:

table1:
LOAD *,
    Left(Header,FindOneOf(Mid(Header,3)&'/',PurgeChar(Mid(Header,3),'0123456789.')&'/')+1) as IndustryCode,
    Mid(Header,FindOneOf(Header,PurgeChar(Header,'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),-1)+1) as Measure
Inline [
    Header
    AA10P
    AA10C
    AA10SPF
    AA11.1P
    AA11.1C
    AA11.1SPF
    AA23.2/45P
    AA23.2/45C
    AA23.2/45SPF
    AB1A
    AB1\B
    AB1.1C
    AB1.1|D
    AB12E
    AB12.3FG
    AB123.45H
    AB123.456/IJ
    BB1
    BB23
    BB34.5
    BB456.7A
    BB5678.90/BC         
];

hope this helps

regards

Marco

Re: QlikView text manipulation question

m_monfort — Wed, 12 Sep 2018 13:22:17 GMT

Thanks everyone! These are great.. Marco's gets closest but there is just one more thing.

Marco, one of the sectors AA23.2/45 needs to be shown as that sector... your code makes it AA23.2 only.

How do I include that last part.. once I've got that I can mark your answer as correct!

Thank you again

Re: QlikView text manipulation question

vishsaggi — Wed, 12 Sep 2018 16:05:27 GMT

Did you try my expr?

Re: QlikView text manipulation question

vishsaggi — Wed, 12 Sep 2018 16:20:48 GMT

LOAD *,

Left(TextVal, Index(TextVal, KeepChar(PurgeChar(TextVal,'AA'), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'), 1)-1) AS NewFieldValue,

KeepChar(PurgeChar(TextVal,'AA'), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') AS MeasureCode INLINE [

TextVal

AA10P

AA10C

AA10SPF

AA11.1P

AA11.1C

AA11.1SPF

AA23.2/45P

AA23.2/45C

AA23.2/45SPF

];

Sorry ignore my expr works only if the values start with AA. Probably you have different start characters right?

Re: QlikView text manipulation question

MarcoWedel — Wed, 12 Sep 2018 20:36:52 GMT

Isn't that contradicting your initial statement that "The unique fields above cover only 3 industry codes, AA10, AA11.1 and AA23.2"?

Re: QlikView text manipulation question

m_monfort — Wed, 12 Sep 2018 22:12:50 GMT

HI Marco - sorry if I was not clear and I appreciate your help as I did not even get 50% of the answer and you guys have done a lot.

The use cases are not only for those numbers but for any type of 2 letter initial region code, a sector number and a measure designation (SPF, C , M ... any combo really).

The examples were only 3 and the output was to be something like AA10, AA11.1 or AA23.2/45... OR it could be UN35.23 or XY55.3/5

each of the above i would call a sector code... any letters that come after are what I call measures

Thanks

Re: QlikView text manipulation question

m_monfort — Wed, 12 Sep 2018 22:13:56 GMT

Hi Vish - you're right - it can have any 2 letter code at the start as the region code

I did try your solution but didn't work for some reason - I'll try again

Re: QlikView text manipulation question

MarcoWedel — Thu, 13 Sep 2018 20:12:48 GMT

So you simply want to purge/extract the last characters from a text that all are letters?

Re: QlikView text manipulation question

m_monfort — Fri, 14 Sep 2018 07:14:44 GMT

Hi Marco - essentially that would be it. Wherever the number ends I need the latter half to be the measure and the first part to be an identifier code.

If it was AU15.11/2XXXX the measure is the XXXX and the AU15.11/2 would be the identifer.

The measure (XXXX) can be any combination of only letters but it can be any length - it is not fixed.

is there a way to handle that?

Re: QlikView text manipulation question

MarcoWedel — Fri, 14 Sep 2018 18:13:16 GMT

sounds like this could be a solution:

table1:
LOAD *,
    Left(Header,FindOneOf(Header,PurgeChar(Header,'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),-1)) as IndustryCode,
    Mid(Header,FindOneOf(Header,PurgeChar(Header,'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),-1)+1) as Measure
Inline [
    Header
    AA10P
    AA10C
    AA10SPF
    AA11.1P
    AA11.1C
    AA11.1SPF
    AA23.2/45P
    AA23.2/45C
    AA23.2/45SPF
    AB1A
    AB1\2B
    AB1.1C
    AB1.1|23D
    AB12E
    AB12.3FG
    AB123.45H
    AB123.456/789IJ
    BB1
    BB23
    BB34.5
    BB456.7A
    BB5678.90/0.12BC         
];

hope this helps

regards

Marco

Re: QlikView text manipulation question

m_monfort — Sun, 16 Sep 2018 10:14:09 GMT

Perfect! Thanks Marco