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

How to map values with squarebrackets into values without squarebrackets

How to map values with squarebrackets into values without squarebrackets while loading???

the below coding doesnot work..

map1:

mapping load * Inline

[

x,y

"187[32]",'187'

];

can anyone help!!!

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

Hi,

Yes, because there is no index number, but a conditional should work around that

Directory;

Data:

LOAD Left(Field1, If(Index(Field1, '[') -1 <= 0, Len(Field1), Index(Field1, '[') -1))  AS Field1

FROM

test.txt

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

So you keep using Left() but when there is no bracket, then you take all characters from value, otherwise only until the bracket.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

View solution in original post

12 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi

   You can use the function called mapsubstring().

   Look attachment for example.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Miguel_Angel_Baeyens

Hi,

Use the PurgeChar() string function in your load script to get rid of unwanted characters:

Table:

LOAD Field1,

     PurgeChar(Field1, '[]') AS Field1Clean

FROM ...

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

gussfish
Creator II
Creator II

Looks like you've hit upon a QlikView bug, to me.

A workaround would be to use something other than square brackets in

your data, and then use an expression to massageit into the desired

form.

e.g.

map1:

mapping

load

replace(replace(x,'{','['),'}',']') AS x,

y

Inline

[

x,y

"187",'187'

];

Not applicable
Author

The Field1 has 3 records...

1st record---- 1111[nb 2]

2nd record--- 2222[4]

3rd record---- 3333[4]

I want to remove those square brackets and its content..it should be like 1111,2222,3333

so i used,

map1:

mapping

load * Inline

[

x,y

[nb 2]],''   Comment:---> if i use [nb 2],''---->syntaxerror--->so [nb 2]]--->nosyntax error

[4]],''        Comment: ---> But not correct output, See the Final Output given below

];

Sample:

LOAD

     MapSubString('map1',Field1) as Field1

FROM

[Sample.xls]

(qvd) Where 1=1;

Error Generated.....

Garbage after statement

map1:

mapping

load * Inline

[

x,y

[nb 2]],''

[4]],''

]

Final Output.....

1111]

2222[4]

3333[4]

Miguel_Angel_Baeyens

Hello,

Check this file not using MapSubString (I don't think you need it here). Just make sure you want to remove everything after the brackets:

Left('2222[4]', Index('2222[4]', '[') -1)

So the script will look like the following

Directory;

Data:

LOAD Left(Field1, Index(Field1, '[') -1) AS Field1

FROM

test.txt

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

On the other hand, two considerations:

  • When you load INLINE, the start and end of data is marked by square brackets, so you cannot use them as values, and that's why you are getting that "Garbage after statement", because QlikView reads the next "]" and understands that the INLINE load finishes there, that is not correct.
  • When you want to mark a line of script as comment, use // instead of ''

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Not applicable
Author

PurgeChar removes only the square bracket..I want to remove both the square bracket and its content ...

eg: 1111[nb 2] into 1111...

Miguel_Angel_Baeyens

HI,

When I realized that what you were expecting was not that, I gave an alternate solution in my post above.

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Not applicable
Author

Hello,

@Miguel...Sorry...I didn't notice ur 2nd Post...

Not applicable
Author

Hi..

I got it...ur code worked perfect...