Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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
MVP
MVP

Re: How to map values with squarebrackets into values without squarebrackets

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

12 Replies

Re: How to map values with squarebrackets into values without squarebrackets

Hi

   You can use the function called mapsubstring().

   Look attachment for example.

Regards,

Kaushik Solanki

MVP
MVP

Re: How to map values with squarebrackets into values without squarebrackets

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
Contributor II

Re: How to map values with squarebrackets into values without squarebrackets

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

Re: How to map values with squarebrackets into values without squarebrackets

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]

MVP
MVP

Re: How to map values with squarebrackets into values without squarebrackets

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

Re: How to map values with squarebrackets into values without squarebrackets

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

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

Highlighted
MVP
MVP

Re: How to map values with squarebrackets into values without squarebrackets

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

Re: How to map values with squarebrackets into values without squarebrackets

Hello,

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

Not applicable

Re: How to map values with squarebrackets into values without squarebrackets

Hi..

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

Community Browser