Has anyone had any luck using SQL to clean up CSI data that comes from TNEW custom forms?
We are using custom form data to collect information on our travel program participants when they register for a trip, which is great! However, when you run a report on CSIs and export it, all the collected data comes across as one cell in excel. I've used column splits in power query to separate everything out, but we'd love for the data to feed directly into a tableau, rather than being exported and then power queried. I'm pretty new to SQL and completely self taught, so I didn't know if there was some way to make SQL do what the power query is doing. The folks actually looking at and using the data don't use power query and only barely get into Tess for anything, so they currently can't get at the data themselves and certainly can't get it into a usable format.
Any advice would be appreciated!
Thanks!
The easiest solution I can think of is have run the CSI report and simply save it as a CSV file. When I do this for our CSI Tracking Report, all the data shows up in their respective columns in Excel.
Sure, it would be pretty simple to create a report to spit out the data in a specific format and assuming you have some sort of delimiter for the notes field, that should be no issue too. T_CUST_ACTIVITY is the table of interest. To find the description of some ids look in the tables below. Being that I am not 100% sure of the data in your CSI's and I'm hesitant to offer much more. Feel free to email though.
select * from dbo.TR_CUST_ACTIVITY_CATEGORY select * from dbo.TR_CUST_ACTIVITY_TYPE select * from dbo.TR_SPECIAL_ACTIVITYselect * from tr_originselect * from tr_contact_type
TNEW has a Carriage Return (char(13)) as the delimiter
SELECT value FROM STRING_SPLIT((select notes from t_cust_activity where activity_no = 18300), char(13))
took my entry that looks like
Contact_details: HEATH WILDER PO BOX 1092 BROADWAY, NSW (227) 2007 Phone_num: +61414310104 Dietary_req: test Comments: Line Item ID: 44567 Customer Number: 183407 Order Number: 43360
and turns it into ...
Hope that helps
(That's an old address for anyone who is planning on sending me a cake)
There's something else you can do with TNEW, which is to assign a stored procedure to receive the custom form data instead of letting TNEW create the CSI for you. This is often the cleanest option for this sort of thing; perhaps even having the SQL proc write the data into a custom table whose fields match the custom form. But, it does require some careful coding.
Have you figured out a simple way to pivot a whole bunch of CSI’s up onto a single lives per csi?
That sounds like a challenge
I've made a local a local table-valued function that uses FT_SPLIT_LIST to also output the activity_no as well as ElementID and Element fields. You just use a cursor to move down and select the activity_no from T_CUST_ACTIVITY using a filter like activity_type, for example.
With this local function, I can join it to itself as often as there are ElementID rows per activity_no, each join is a different ElementID. While this could be placed in a view, we have so many so I'm opting for a nightly procedure to populate a local table, instead. This will join back to T_CUST_ACTIVITY to tie back to customer_no.
I've done what Nick suggested at a past organisation, i.e. populate a table instead of using CSIs, however, that was for a brand new project. Another consideration is you can only pass up to 32 parameters (populate up to 32 fields) unless that has changed recently.
Thanks everyone for the suggestions! I've got some ideas to try now.
Coming late to the party, but I would also encourage a stored procedure on this one, one that probably puts the data into a local table rather than the standard CSI storage location. Especially if you are looking to export the data and/or do other SQL processes on it anyway.
Kevin would it be too much trouble to take a peek at your function?
Hi Heath, I hadn't seen a notification re your last reply, do you want to email me at kmadeira@npg.org.uk?