Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
Hi sunny,
No the part numbers are 3 separate rows but in a single column... My exected output wud b 41B541600A, 156B6202CG, 12A103598..
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?
Yes it should be...by mistake i typed it..
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