topic Re: Data Clean in QlikView

Data Clean

Tue, 04 Apr 2017 10:14:47 GMT

I am extracting the data from share point and some fields contains the data like below. I have Attached sample data as well. I need load only the bold characters into QLik.

div class="Exter<div class="ExternalClass30F90EFDF60948D7A18F8E88020C0F89">03/09 : Yet to identify projects</div>
<div class="ExternalClass30F90EFDF60948D7A18F8E88020C0F89">03/10 : Profile shared for insurance.</div>
<div class="ExternalClass30F90EFDF60948D7A18F8E88020C0F89"><div>03/29: Profiles shared for again as already two seniors in team.</div>
</div>lClass30F90EFDF60948D7A18F8E88020C0F89"

Re: Data Clean

Anonymous — Tue, 04 Apr 2017 10:21:08 GMT

Directory;

LOAD TextBetween(@1,'>','<')

FROM

[Data cleaning.xls]

(biff, no labels, table is sample$);

Re: Data Clean

Tue, 04 Apr 2017 10:30:51 GMT

Hi ,

Thanks!! It's not loading the all the data .. the data contains multiple lines as well. it's not working out.

Re: Data Clean

Anonymous — Tue, 04 Apr 2017 10:35:02 GMT

Oh ok. Do you want 03/09: Yet to identify projects,03/10: Profile shared for insurance.,03/29: Profiles shared for again as already two seniors in team concatenated as a single record?? like wise you need to get the records for all the cells in the excel??

Re: Data Clean

Tue, 04 Apr 2017 10:38:15 GMT

Yes. That's right !. We need to capture the same for all the records .

(03/09: Yet to identify projects,

03/10: Profile shared for insurance.,

03/29: Profiles shared for again as already two seniors in team.) ---- single record ..like this

....

Re: Data Clean

Anil_Babu_Samineni — Tue, 04 Apr 2017 10:52:48 GMT

I am sure, marcowedel‌ or cleveranjos‌ can help us

Re: Data Clean

Anonymous — Tue, 04 Apr 2017 12:25:57 GMT

Try this.

set errormode = 0;

temp:

LOAD RecNo() as ID,@1 as text//concat(TextBetween(@1,'>','<',1),chr(13))

FROM

[Data cleaning.xls]

(biff, no labels, table is sample$);

test:

Load *,substringcount(text, '>') as Maxcount Resident temp;

drop table temp;

FOR Each Id in FieldValueList('ID')

Let MaxCount = FieldValue('Maxcount',$(Id));

LET String = Replace(FieldValue('text',$(Id)),chr(39),'$#@');

FOR loop =1 to $(MaxCount)

splitted_data:

Load $(Id) as Rownumber ,Replace(TextBetween('$(String)','>','<',$(loop)),'$#@',chr(39)) as String

AutoGenerate(1);

output:

Load Trim(Concat(String,chr(13))) Resident splitted_data group by Rownumber;

Drop tables test,splitted_data;

Re: Data Clean

MarcoWedel — Fri, 07 Apr 2017 00:32:34 GMT

Hi,

besides looking for a better way to export your data from SharePoint, one solution using this export format might be:

table1:
LOAD CommentID,
    Comments,
    AutoNumber(SubCommentNo,CommentID) as SubCommentNo,
    SubComment
Where Len(SubComment);
LOAD RecNo() as CommentID,
    Comments,
    IterNo() as SubCommentNo,
    Trim(PurgeChar(TextBetween(Comments,'>','<',IterNo()),Chr(160))) as SubComment
FROM [https://community.qlik.com/servlet/JiveServlet/download/1244571-272999/Data%20cleaning.xls] (biff, embedded labels, table is sample$)
While IterNo()<=SubStringCount(Comments,'>');

hope this helps

regards

Marco

Re: Data Clean

Mon, 10 Apr 2017 14:49:27 GMT

Thanks Marco . It will workout..

Re: Data Clean

MarcoWedel — Tue, 11 Apr 2017 10:21:54 GMT

You're welcome.

Glad it worked.

Regards

Marco