Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
As a part of cleaning my Big Data text, I have to replace multiple stings to blank (I have almost 40 characters/strings to replace blank).
I am using
LOAD
...
Replace(‘replace_field','string_A',’’) as ReplacedString,
...
FROM ...;
Can I replace all my strings (example: string_B, String_C, String_D etc) to blank in single command?
@ Robert
I love to explore this. Can you help me in the scenario if I have more than one field in my data set?
Example:
I have SalesOrder_ID, SalesOrder_BP and SalesOrder_Text (Here I want to change/replace strings only Sales Order Text field)
Thanks for your help.
Source:
load str inline [
str
Hello how are you
123aaa123
123bbb123
Say hello
Say Hello
123 Hello Hello 123
];
// string to replace with blank
MapString:
Mapping LOAD * inline [
from, to
aaa,
bbb,
Hello,
];
//
Final:
load
*,
MapSubString('MapString', str) as newstr
Resident
Source;
DROP Table Source;
Script:
map1:
mapping load * inline [
x, y
Sales, ''
Order, ''
Text,'' ] ;
Directory;
LOAD mapsubstring('map1',String) as String
FROM
[152744.xlsx]
(ooxml, embedded labels, table is Sheet1);
@ Robert sorry I was not explained well in my previous post...
Current Data
SaelsOrder_ID | SalesOrder_BP | SalesOrder_Text | ||
1001 | 220001 | this is Tuesday | ||
1002 | 220001 | Customer is not happy | ||
1003 | 220001 | Customer is happy | ||
1004 | 220002 | very good dinner | ||
1005 | 220003 | I have a quick question | ||
1006 | 220004 | kids are not happy | ||
1007 | 220005 | uncle is not at dinner | ||
Replace | happy' | |||
dinner' | with BLANK | |||
Desired output | ||||
SaelsOrder_ID | SalesOrder_BP | SalesOrder_Text | New Text (Result) | |
1001 | 220001 | this is Tuesday | this is Tuesday | (no change) |
1002 | 220001 | Customer is not happy | Customer is not | |
1003 | 220001 | Customer is happy | Customer is | |
1004 | 220002 | very good dinner | very good | |
1005 | 220003 | I have a quick question | I have a quick question | (no change) |
1006 | 220004 | kids are not happy | kids are not | |
1007 | 220005 | uncle is not at dinner | uncle is not at |
map1:
mapping load * inline [
x, y
happy, ''
dinner, ''
] ;
Directory;
LOAD
SaelsOrder_ID,
SalesOrder_BP,
SalesOrder_Text,
mapsubstring('map1',SalesOrder_Text) as Changed
FROM
[152744.xlsx]
(ooxml, embedded labels, table is Sheet1);
@ Robert...Thanks you so much!!! This is a wonderful function and we are able to cleanse our data or bring our data from 20+ GBs to 3+ GB. We end up with cleaning lot more strings than we anticipated but this is very handy. Again thank you for all this community help. Even though we purchased Qlik very recent, we feel like we have great support and I feel that we can rock in Qlik!!!
@ Robert One more question...after cleansing my data, I did see lot of white spaces in my text box. Is there a way I can clean that white spaces? (BTW I don't want to clear the white spaces between words)., I did see a similar solution in Qlik Community but I am not sure how I apply to this. Remove blank spaces between a string
Thanks for your help.
You can use TRIM,LTRIm or RTRIM but if you provide an example that would help to give you more accurate answer.
@Robert After I ran above cleanse program, I got below sample file. But I would like get rid of empty spaces. is it possible?
Sample in the file:
///////////////////////////////////////////////////////////
c c 1c
'02'00. '01
'02'01. '01
'02'02. '01
//////////////////////////////////////////////////////////////
My desired result is below...
////////////////////////////////////////////////////////////
c c 1c
'02'00. '01
'02'01. '01
'02'02. '01
////////////////////////////////////////////////////////////////
Try this:
Directory;
LOAD
Data,
if(len(Trim(Data))>0 ,Trim(Data))
FROM
[152744.xlsx]
(ooxml, embedded labels, table is Sheet3);