Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Not applicable

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

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

6 Replies
sjw
Not applicable

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

Is this question for QlikView or Qlik Sense?

It doesn't seem like an NPrinting question.

Not applicable

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

Hi Stephen,


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

Thanks,

Chaithra

sjw
Not applicable

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

I've moved your post to the Scripting forum

MarcoWedel
Not applicable

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

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

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

Hi Marco,

It is giving results as expected, thank you.

Thanks,

Chaithra

MarcoWedel
Not applicable

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

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