Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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.
BI Consultant
Hi
You can use the function called mapsubstring().
Look attachment for example.
Regards,
Kaushik Solanki
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.
BI Consultant
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'
];
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]
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:
Hope that helps.
BI Consultant
PurgeChar removes only the square bracket..I want to remove both the square bracket and its content ...
eg: 1111[nb 2] into 1111...
HI,
When I realized that what you were expecting was not that, I gave an alternate solution in my post above.
Regards.
BI Consultant
Hello,
@Miguel...Sorry...I didn't notice ur 2nd Post...
Hi..
I got it...ur code worked perfect...