topic Re: Need quick help on string manipulation in QlikView

Need quick help on string manipulation

praveensprabhu — Wed, 25 Nov 2020 16:16:04 GMT

Hi Folks,

I am new to qlik, so i would be needing help on a string manipulation in load script.

I have a column in my table named "Part Number" which has values like '41B541600APP9','156B6202CGP36G','12A103598P3'. I want to get the substring before the occurence of P or G from right.

I have used following nested if to achieve this

if((Index(PartNumber,'P',-1)<>-1),mid(PartNumber,1,Index(PartNumber,'P',1)-1),
if((Index(PartNumber,'G',-1)<>-1),mid(PartNumber,1,Index(PartNumber,'G',-1)-1),PartNumber))

but it is working only for P but not for G. Could anyone help me to solve this issue?

Thanks in advance.

Regards,

Praveen Prabhu

Re: Need quick help on string manipulation

sunny_talwar — Tue, 19 Jun 2018 16:44:34 GMT

The three codes you have, are all from one row of data? What would be the expected output for the data that you have shared?

Re: Need quick help on string manipulation

praveensprabhu — Tue, 19 Jun 2018 17:01:09 GMT

Hi sunny,

No the part numbers are 3 separate rows but in a single column... My exected output wud b 41B541600A, 156B6202CG, 12A103598..

Re: Need quick help on string manipulation

sunny_talwar — Tue, 19 Jun 2018 17:44:23 GMT

May be try this

Left(PartNumber, RangeMin(If(Index(PartNumber, 'P') = 0, Len(PartNumber), Index(PartNumber, 'P')), If(Index(PartNumber, 'G') = 0, Len(PartNumber), Index(PartNumber, 'G')))-1) as PartNumber,

Only thing I don't understand is why the second part is 156B6202CG and not 156B6202C? Because G comes before P, so, it should stop before G, right?

Re: Need quick help on string manipulation

praveensprabhu — Tue, 19 Jun 2018 17:50:53 GMT

Yes it should be...by mistake i typed it..

Re: Need quick help on string manipulation

MarcoWedel — Tue, 19 Jun 2018 20:45:56 GMT

Hi,

one solution might be:

table1:
LOAD *,
    Left(PartNumber,FindOneOf(PartNumber&'P','PG')-1) as SubstringBeforePorG
Inline [
    PartNumber
    41B541600APP9
    156B6202CGP36G
    12A103598P3
    12345ABC678DEF9
];

hope this helps

regards

Marco