topic Re: Add hyphen to a string value based on the mapping file in QlikView

Add hyphen to a string value based on the mapping file

Wed, 26 Oct 2016 17:14:48 GMT

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.

Re: Add hyphen to a string value based on the mapping file

Stephen_Jasionowski — Wed, 26 Oct 2016 17:21:02 GMT

Is this question for QlikView or Qlik Sense?

It doesn't seem like an NPrinting question.

Re: Add hyphen to a string value based on the mapping file

Wed, 26 Oct 2016 17:28:12 GMT

Hi Stephen,

This question is for Qlikview, how can i correct this.

Thanks,

Chaithra

Re: Add hyphen to a string value based on the mapping file

Stephen_Jasionowski — Wed, 26 Oct 2016 19:26:18 GMT

I've moved your post to the Scripting forum

Re: Add hyphen to a string value based on the mapping file

MarcoWedel — Wed, 26 Oct 2016 23:09:56 GMT

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

Re: Add hyphen to a string value based on the mapping file

Thu, 27 Oct 2016 05:23:28 GMT

Hi Marco,

It is giving results as expected, thank you.

Thanks,

Chaithra

Re: Add hyphen to a string value based on the mapping file

MarcoWedel — Thu, 27 Oct 2016 21:16:59 GMT

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