Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to add hyphen to a string value and the position of the hyphen has to considered from the mapping file.
From the input file,Lookup the first 3 char of the SKU against the mapping file.
Based on the length mentioned for the corresponding entry in mapping file, add "-" at xth position from the end, where x is the length defined in the mapping file.If it doesn't find a match in the mapping file, input string value has to retained as is.
Below is the example for reference.
Input file:
SKU |
---|
CPC12345 |
CPDE456 |
12345 |
Mapping File:
SKU Category | Length |
---|---|
CPC | 3 |
CPD | 4 |
ABG | 8 |
Expected output:
SKU |
---|
CPC123-45 |
CPDE-456 |
12345 |
Thanks in advance.
Hi,
one solution might be:
mapSKU:
Mapping LOAD * FROM [https://community.qlik.com/thread/237767] (html, codepage is 1252, embedded labels, table is @2);
tabInputFile:
LOAD *, If(ApplyMap('mapSKU',Left(SKU,3),Null()),Left(SKU,Len(SKU)-ApplyMap('mapSKU',Left(SKU,3))+1)&'-'&Right(SKU,ApplyMap('mapSKU',Left(SKU,3))-1),SKU) as SKU_New
FROM [https://community.qlik.com/thread/237767] (html, codepage is 1252, embedded labels, table is @1);
hope this helps
regards
Marco
Is this question for QlikView or Qlik Sense?
It doesn't seem like an NPrinting question.
Hi Stephen,
This question is for Qlikview, how can i correct this.
Thanks,
Chaithra
I've moved your post to the Scripting forum
Hi,
one solution might be:
mapSKU:
Mapping LOAD * FROM [https://community.qlik.com/thread/237767] (html, codepage is 1252, embedded labels, table is @2);
tabInputFile:
LOAD *, If(ApplyMap('mapSKU',Left(SKU,3),Null()),Left(SKU,Len(SKU)-ApplyMap('mapSKU',Left(SKU,3))+1)&'-'&Right(SKU,ApplyMap('mapSKU',Left(SKU,3))-1),SKU) as SKU_New
FROM [https://community.qlik.com/thread/237767] (html, codepage is 1252, embedded labels, table is @1);
hope this helps
regards
Marco
Hi Marco,
It is giving results as expected, thank you.
Thanks,
Chaithra
You're welcome.
Nice to hear that it worked.
Please close your thread if your question is answered:
Qlik Community Tip: Marking Replies as Correct or Helpful
thanks
regards
Marco