Data Clean

[#1]

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"

[Anonymous]

Directory;

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

FROM

[Data cleaning.xls]

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

[#3]

Hi ,

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

[Anonymous]

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

[#5]

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

....

[Anil_Babu_Samineni]

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

[Anonymous]

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);

  next

next

output:

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

Drop tables test,splitted_data;

[MarcoWedel · Accepted solution]

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

[#9]

Thanks Marco . It will workout..

[MarcoWedel]

You're welcome.

Glad it worked.

Regards

Marco