Data loading with extra characters - need to trim

[Anonymous]

I am loading my data from a Sharepoint 2013 site -

1) Web Files

(http://prime21.sharepoint.hp.com/teams/CloseIssue/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=0&RowLimit...

2) File Type choose Xml and in the Table tab choose "xml/data/row"

When the data loads, several of the fields have extra characters in the FIELD VALUES( ie: string;#, <div></div>). If I load the SAME data in to QV from an Excel file that is Exported from the Sharepoint the data is correct - no "extra characters"

When I use PurgeChar or Mid or TRIM(RTrim or LTrim) in the Expression for that ListBox Properties - it adds the edited data in the NULL field.  Expression used- =mid([Close Period],9,9)

I would prefer to fix the data via the load script as I will have several sheets that will use these Data fields.

[morgankejerhag]

I cannot connect to your source, so I can not verify it myself, but I would use different ways of removing bad values in the script like you want.

What happens if you load for example replace([Close Period],'string;#','') ? This should replace the "string;#" with an empty string.

All the functions you mentioned (purgechar, mid, trim) work in the script as well.

If you upload an example application we can help out better.

[Anonymous]

Morgan,

Attached is the QVW that I have created so far.

I am new to Qlikview and this is my 1st dashboard that I am creating.

Hopefully you will be able to see the data as I am seeing it in the CLOSE PERIOD field.  There are other fields that contain additional characters also - PROBLEM SOURCE APPLICATION - ISSUE UPDATE that I will eventually want to parse out as well. 

In the ISSUE UPDATE field, on the Sharepoint from which this data is pulled looks like this:

Update 3: 11/30/2012 16:30 CST  Joe Smith BRM
Today, ARC reported missing invoices from Nov 29th, Support team checked and found 160K idocs pending to be processed in LH1234, they restarted the job that handles these idoctors and the current backlog had begun to decrease.

Update 2: 11/30/2012 00:15 CST Joe Smith BRM
Of the 186 missing invoices, only 2 idoctors have fallen out in LH1234 due to missing Customer data. Biz has been informed to have this master data fixed. All the other invoices are already visible in LH1234. IT used the TDE Code FB03 and the ABC Reference numbers provided for these invoices in the ticket.

Update 1: 11/29/2012 21:00 CST Joe Smith BRM
Business reported of missing 186 invoices from PL1234 to LH1234. Initial investigation by  LH1234 team indicates that there are only 2 fallouts in LH123 due to Customer missing. the other fallouts are not seen. Need to engage the XIPW team

I see the data in QV as - <div><strong>Update 3: 11/30/2012 16:30 CST Joe Smith BRM</strong></div> <div>Today, ARC reported missing invoices from Nov 29th, Support team checked and found 160K idocs pending to be processed in LH1234, they restarted the job that handles these idoctors and the current backlog had begun to decrease..</div> <div><strong></strong> </div> <div><strong>Update 2: 11/30/2012 00:15 CST Joe Smith</strong></div> <div>Of the 186 missing invoices, only 2 idoctors have fallen out in LH1234 due to missing Customer data. Biz has been informed to have this master data fixed. All the other invoices are already visible in LH1234. IT used the TDE Code FB03 and the ABC Reference numbers provided for these invoices in the ticket.<br /><br /><strong>Update 1: 11/29/2012 21:00 CST Joe Smith</strong></div> <div>Business reported of missing 186 invoices from PL1234 to LH1234. Initial investigation by  LH1234 team indicates that there are only 2 fallouts in LH123 due to Customer missing. the other fallouts are not seen. Need to engage the XIPW team</div>

I am not sure how and where the REPLACE statement should go in the LOAD script, but I tried several methods and it did not work.

Any help is greatly appreciated!

Dan

[morgankejerhag · Accepted solution]

Hello Dan,

I am unable to reload your application, but I added some example expressions to the sheet Ark4 and to the script (untested). After reload you don't need the expressions in Ark4 as the columns should be cleaned. Look at the attached file.

[Anonymous]

Morgan,

The expressions that you provided for the Load Script worked perfectly   and I was able to use these strings for other fields that had garbage characters as well.

There is one last field that I am having problems with and was wondering if you can help with this one also.  This field is adding 1 to 3 Numbers and then ;# before the valid data.

I was able to remove the ";" & '#" with this expression, but cannot figure out how to TRIM the NUMBERS.

              replace(

                replace(

                [ows_Problem_x0020_Source_x0020_Appli]

                ,';','')

                ,'#','') as [Problem Source Application],

Any suggestions on how to trim the numbers and then the ";" & "#" ?

Thanks!

Dan

[morgankejerhag]

It looks like there is always a # before the real text? You could do something like

Mid(Field, index(Field, '#'))

You might need to add some +1 on the index

[Anonymous]

Morgan,

This worked out perfectly with adding the +1!!!!!

Last one if you would be so kind.  I have this CLOSE MONTH field that I would like to show up as the MONTH instead of 2012-09.

Current

Would just like it to show as Month(ie: December).

Thanks again!

Dan

[morgankejerhag]

In this case you should convert the period to a date and then get the month from that. For this you could do something like this:

SubField('$(MonthNames)',';',right(DateField,2))

Replace DateField with the correct field name. The variable MonthNames is a standard variable that is usually defined at the top of the sript. You can replace it for one of your own if you prefere.

Another option is to convert the period to a date. From that date you can derive year, quarter etc. Here is how to get the year:

year(makedate(left(DateField,4),right(DateField,2)))