topic Re: How to map values with squarebrackets into values without squarebrackets in QlikView

How to map values with squarebrackets into values without squarebrackets

Tue, 21 Jun 2011 06:38:22 GMT

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

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

kaushiknsolanki — Tue, 21 Jun 2011 06:56:03 GMT

You can use the function called mapsubstring().

Look attachment for example.

Regards,

Kaushik Solanki

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

Miguel_Angel_Baeyens — Tue, 21 Jun 2011 07:00:04 GMT

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

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

gussfish — Tue, 21 Jun 2011 07:36:21 GMT

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,

Inline

[

x,y

"187",'187'

];

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

Wed, 22 Jun 2011 07:26:32 GMT

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]

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

Miguel_Angel_Baeyens — Wed, 22 Jun 2011 07:35:49 GMT

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

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

Wed, 22 Jun 2011 08:42:14 GMT

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

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

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

Miguel_Angel_Baeyens — Wed, 22 Jun 2011 08:52:10 GMT

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

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

Wed, 22 Jun 2011 09:01:42 GMT

Hello,

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

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

Wed, 22 Jun 2011 09:04:44 GMT

Hi..

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

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

Wed, 22 Jun 2011 09:10:32 GMT

Field1 also has values without square brackets...those values are not listed..

Eg:

1111[nb 2]

2222[4]

3333[4]

4444

5555

On using Left( ), Output is

1111

2222

3333

4444 and 5555 are not listed....

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

Miguel_Angel_Baeyens — Wed, 22 Jun 2011 09:15:01 GMT

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

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

Wed, 22 Jun 2011 09:49:55 GMT

Thanks Dude...