Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Add hyphen to a string value based on the mapping file

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 CategoryLength
CPC3
CPD4
ABG8

Expected output:

SKU
CPC123-45
CPDE-456
12345

Thanks in advance.

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one solution might be:

QlikCommunity_Thread_237767_Pic1.JPG

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

View solution in original post

6 Replies
Stephen_Jasionowski

Is this question for QlikView or Qlik Sense?

It doesn't seem like an NPrinting question.

Not applicable
Author

Hi Stephen,


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

Thanks,

Chaithra

Stephen_Jasionowski

I've moved your post to the Scripting forum

MarcoWedel

Hi,

one solution might be:

QlikCommunity_Thread_237767_Pic1.JPG

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

Not applicable
Author

Hi Marco,

It is giving results as expected, thank you.

Thanks,

Chaithra

MarcoWedel

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