topic Re: Misplaced Comma in a field variable in QlikView

Misplaced Comma in a field variable

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Misplaced Comma in a field variable

Fri, 24 Feb 2017 02:19:36 GMT

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

Re: Misplaced Comma in a field variable

Fri, 24 Feb 2017 03:51:52 GMT

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

Re: Misplaced Comma in a field variable

hector_munoz — Fri, 24 Feb 2017 04:08:08 GMT

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,

Re: Misplaced Comma in a field variable

Fri, 24 Feb 2017 04:44:43 GMT

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

Re: Misplaced Comma in a field variable

nagarjuna_kotha — Fri, 24 Feb 2017 04:59:11 GMT

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

-Nagarjuna

Re: Misplaced Comma in a field variable

rahulpawarb — Fri, 24 Feb 2017 05:33:32 GMT

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

Re: Misplaced Comma in a field variable

hector_munoz — Fri, 24 Feb 2017 08:39:09 GMT

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,

Re: Misplaced Comma in a field variable

hector_munoz — Fri, 24 Feb 2017 09:00:53 GMT

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

Re: Misplaced Comma in a field variable

tcullinane — Fri, 24 Feb 2017 11:23:55 GMT

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

];

Re: Misplaced Comma in a field variable

Peter_Cammaert — Fri, 24 Feb 2017 12:29:46 GMT

A LOAD INLINE statement allows the optional specification of another delimiter character. You can set it to some character that you are sure it doesn't occur in your input data. Like for example, a tab character in

Mapping LOAD * INLINE [
RAAScheme_CoverType    Group
Comprehensive - Car    Comp_Car
Comprehensive - Lifetime Vehicle Replacement    Comp_Car
Comprehensive - Caravan    Comp_Cvn

Comprehensive - Mobility Scooter, Wheelchair Something
] (delimiter is '\t');

That way, the scripting engine will not treat commas as separators.

Note: everything - except $-sign substitutions - between the square brackets is treated as text. No expressions are evaluated.

Re: Misplaced Comma in a field variable

Mon, 27 Feb 2017 02:33:19 GMT

Hey Pete

If I could give you a hug I would, worked a treat thanks a million

Re: Misplaced Comma in a field variable

oknotsen — Mon, 27 Feb 2017 08:32:07 GMT

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .

Re: Misplaced Comma in a field variable

Tue, 07 Mar 2017 04:43:37 GMT

Where is this so called correct answer button located, I even asked a couple of colleagues to see if they could see one which you make reference to and they both came up blank!!!!

Re: Misplaced Comma in a field variable

Anil_Babu_Samineni — Tue, 07 Mar 2017 04:48:08 GMT

Qlik Community Tip: Marking Replies as Correct or Helpful

Re: Misplaced Comma in a field variable

Tue, 07 Mar 2017 05:33:19 GMT

Did that previously once I had the answer and said all was ok so not sure what's going on

Re: Misplaced Comma in a field variable

Anil_Babu_Samineni — Tue, 07 Mar 2017 05:36:45 GMT

Let it be