Misplaced Comma in a field variable

[#1]

I am relatively new to Qlik and my problem is around and issue with a field variable which I believe Qlik is treating as a No-Map issue from a Mapping Table.

I have a field called CoverType, within this filed there is a value Comprehensive - Mobility Scooter, Wheelchair (please note the comma after scooter is part of the value and not the map name. When I include this entry in a mapping table (eg Comprehensive - Mobility Scooter, Wheelchair, Comp_MSW) it returns an No-Map value. I understand why it is occurring (the comma after Scooter).

I have reviewed a number of other potential solutions online however, unfortunately I believe that it is one of these cases where the filed value with its unfortunately placed comma is not lending it to any solution that I have come across. I do have a solution to overcome this however it will create a significant amount of work  and was hoping the Qlik guru's out there may have some solution to this dilemma.

Cheers in advance Wishman

[#2]

Hi ,

While your doing mapping with this Comprehensive - Mobility Scooter, Wheelchair  ,it may not get mapping data

so try to  remove comma using  purgechar(Comprehensive - Mobility Scooter, Wheelchair ,',')

if possible could pls share your application

[#3]

Hi Manoj

I need to remove the comma after scooter not wheelchair!!!

I have added the following in the mapping table (refer bold line below) but still not working , Im assuming the syntax Im using is incorrect?

Mapping LOAD * INLINE [
RAAScheme_CoverType, Group
Comprehensive - Car, Comp_Car
Comprehensive - Lifetime Vehicle Replacement, Comp_Car
Comprehensive - Caravan, Comp_Cvn
purgechar(Comprehensive - Mobility Scooter,',' Wheelchair), Comp_MSW
Comprehensive - Motorcycle, Comp_Mcy

[hector_munoz]

Hi Peter,

Try to put double quotes in description field; I have tried a similar case of yours and it seems to work:

MAP_TYPES:

MAPPING LOAD * INLINE [

CoverType, CoverType_ID

"Comprehensive - Mobility Scooter, Wheelchair", 1

"A, b, c, d, e, f", 2

];

LET vsTest = ApplyMap('MAP_TYPES', 'Comprehensive - Mobility Scooter, Wheelchair', 0);

TRACE $(vsTest);

LET vsTest = ApplyMap('MAP_TYPES', 'A, b, c, d, e, f', 0);

TRACE $(vsTest);

SLEEP 1000;

I hope it serves...

Regards,

H

[#5]

Hi H

I assume you meant to put the double quotes as follows, if so no luck.

Mapping LOAD * INLINE [
RAAScheme_CoverType, Group
Comprehensive - Car, Comp_Car
Comprehensive - Lifetime Vehicle Replacement, Comp_Car
Comprehensive - Caravan, Comp_Cvn
PurgeChar("Comprehensive - Mobility Scooter,',' Wheelchair"), Comp_MSW

[nagarjuna_kotha]

Try to share sample application or sample data, it will helps you get answer quickly.

-Nagarjuna

[rahulpawarb]

Hello Peter,

Hope that you are doing well!

It would be great if you could share the application with sample data or script snippet. This will help us the find the root cause of this issue and provide you our feedback.

Regards!

Rahul

[hector_munoz]

OK, the you need to LOAD the INLINE TABLE into another and in this step, fix the problem with PurgeChar() function in descripction field. The resulting table is the one is you have to use as a mapping one.

Regards,

H

[hector_munoz]

Try the following:

AUX_MAPPING_TABLE:

LOAD * INLINE [

AUX RAAScheme_CoverType, AUX Group

Comprehensive - Car, Comp_Car

Comprehensive - Lifetime Vehicle Replacement, Comp_Car

Comprehensive - Caravan, Comp_Cvn

"Comprehensive - Mobility Scooter, Wheelchair", Comp_MSW

Comprehensive - Motorcycle, Comp_Mcy];

MAPPING_TABLE:

MAPPING LOAD PurgeChar([AUX RAAScheme_CoverType], ',') AS RAAScheme_CoverType,

             [AUX Group]                               AS Group

RESIDENT AUX_MAPPING_TABLE;

DROP TABLE AUX_MAPPING_TABLE;

Regards,
H

[tcullinane]

You dont need to purge char. To make it easier you can use the insert>load>load inline to enter your values in a table, it will take care of the formatting of the table for you. It will end up like below

Mapping LOAD * INLINE [
RAAScheme_CoverType, Group
Comprehensive - Car, Comp_Car
Comprehensive - Lifetime Vehicle Replacement, Comp_Car
Comprehensive - Caravan, Comp_Cvn
"Comprehensive - Mobility Scooter, Wheelchair", Comp_MSW
Comprehensive - Motorcycle, Comp_Mcy

];