Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

How to reduce the lengthy sentences within a field into smaller ones.

Hi,

i had a field with customer complaints,it has the voice of the customer(i.e Complaints) . its very lengthy and each complaint is different.is there any method available in qlikview to reduce the size of the data into less so that it wil be easier for the analysis

That is when we dumb the data it should automaticaly reduced to the specified type.

i hve attached  the sample copy of my excel file along with this..

Example: In the excel sheet which i have attached there is a column called "CONCERN1" which contains all the complaints in lengthy manner like

VOC-I have booked vehicle in the month of july 2012 and i was informed that vehicle will be delivered by 15 days but now it is two months but still vehicle is not delivered to me.When i contact to dealer they are saying they dont have time speak about vehicle booking.I want to know when i will get the vehicle delivery.Contact no 7587471005

this is very much lenghty and cannot be used as it is in the application so it has to be reduced taking important thing the whole sentence conveys that "Delay in delivery" so how to do this.. Is there any way?

Thanks in Advance

9 Replies
khadeer_sparks
Valued Contributor

Re: How to reduce the lengthy sentences within a field into smaller ones.

there r different ways to reduce, based on ur requirement,

Suppose if u want only first 10 charcters from left then left(ur fieldname,10) same for right.

By using subfield u can get the data which is sepearted by deliminator

suppose  subfield(ur field name,3,'----')

Here 3 is ur wanted area after the deliminator n ----- is ur deliminator

Not applicable

Re: How to reduce the lengthy sentences within a field into smaller ones.

Thank u for the reply,

but as per my requirement it is not applicable bcoz as u can see in my excel data sheet the complaints under the column name "Concern1" varies i.e there is lengthy description given by the customer whch can be conveyed in simple words as i hve mentioned in the example and it should give the accurate meaning.

Thanks.

jonbrough
Valued Contributor

Re: How to reduce the lengthy sentences within a field into smaller ones.

If you want to retain the data for analysis (reporting) you could try turning the way QlikView store just unique values to your advantage.

1. Load in table with long string and an autonumber of the long string as a key

2. Load table into a second linked table that performs a SubString command on the long string that splits it up into each word if ' ' space is set as the second parameter

3. Remove the original long string. The shorter strings will remain in the linked table.

You could probably then display the message on the front end in a pivot table with the chopped up string as a secondary dimension that is pivoted to make it appear horizontally. You will need to sort this dimension by load order, and may need to play around with some additional keys if the ordering is messed up.

Jonathan

Not applicable

Re: How to reduce the lengthy sentences within a field into smaller ones.

Thank u Jonbroughavone,

     

jonbrough
Valued Contributor

Re: How to reduce the lengthy sentences within a field into smaller ones.

Try the attached file. I've left the Concern 1 field there, but it can be dropped once you've tested.

Sorting is a bit tricky, but hopefully works ok.

Jonathan

Not applicable

Re: How to reduce the lengthy sentences within a field into smaller ones.

hello Jonbroughavone,

Thank u for the helpful reply, but i didnt get the expected output. I have attached the screenshot showing what exact output i need from the input. What u have explained is that the whole sentence is broken into single seperate words. but what i need is a short meaningful  summary (i.e OUTPUT showed in screenshot below) of the whole lengthy description (i.e INPUT showed in screenshot below).

screenshot.png

jonbrough
Valued Contributor

Re: How to reduce the lengthy sentences within a field into smaller ones.

What field are these Output strings stored in ?

You seem to want some sort of semantic analysis that reads a freeform sentence and assesses it's meaning. This sounds too ambitous for a software package.

Jonathan

Not applicable

Re: How to reduce the lengthy sentences within a field into smaller ones.

hello Jonathan,

The output strings are nowhere stored , actually  it has to be derived from that input string which is stored in the field  'Conern1' . The interpretation given by u is correct it is a kind of semantic analysis only that reads freeform sentence and assesses it's meaning.

Thank u.

jonbrough
Valued Contributor

Re: How to reduce the lengthy sentences within a field into smaller ones.

Good luck. I think a lot of software vendors and search engines will be interested if you manage to achieve this.

Jonathan

Community Browser