Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Categorizing / Bucketing Data using Excel

Hi,

I have a Field called "Event" with lots of different events. The problem is that they all have different text within them including the company name. I want to be able to filter on the event type which is not possible at the moment.

event dateTickerCompany NameEventEVENT TYPE
20/10/2014SAPG.DESAP (Ordinary Share)Q3 2014 SAP SE Earnings Call
20/10/2014SAPG.DESAP (Ordinary Share)Q3 2014 SAP SE Earnings Release
06/11/2014SIEGn.DESiemens AGFull Year 2014 Siemens AG Earnings Conference Call
06/11/2014SIEGn.DESiemens AGFull Year 2014 Siemens AG Earnings Release
11/11/2014TW.LTaylor WimpeyTaylor Wimpey PLC Interim Management Statement Release
12/11/2014HAYS.LHays plcHays PLC Annual Shareholders Meeting
12/11/2014WBSV.VIWienerbergerQ3 2014 Wienerberger AG Earnings Release
09/12/2014SIEGn.DESiemens AGSiemens AG Capital Market Day "Vision 2020"
08/01/2015HAYS.LHays plcHays plc Trading Update for Quarter Ending 31 December 2014

Would be great if I could create a new field Called "Event Type"  and have the ability to filter on unique Event Type.

Would be useful if I could bucket these categories in an Excel file ?

i.e   State the search string in one column and the final "Event Type" in another.

Search textEvent Type
Earnings CallEarnings
Earnings ReleaseEarnings
Earnings Conference CallEarnings
Interim Management StatementInterim Management Statement
Capital Market DayCapital Market Day

Can anyone please help on this?

Many thanks,

T

1 Solution

Accepted Solutions
MVP
MVP

6 Replies
MVP
MVP

maybe this; non matching event are excluded


a:

LOAD [event date], Ticker, [Company Name], Event //, [EVENT TYPE]

FROM [http://community.qlik.com/thread/138820] (html, codepage is 1252, embedded labels, table is @1);

join (a)

LOAD [Search text], [Event Type]

FROM [http://community.qlik.com/thread/138820] (html, codepage is 1252, embedded labels, table is @2);

final:

NoConcatenate

load *

Resident a

where index(Event, [Search text]) > 0;

DROP Table a;

Not applicable

Is there no way I can set up an excel spreadsheet  with the "search text"  in one column and the new "Event Type in another column"   Maybe using the MAP function?

MVP
MVP

Not applicable

This is exactly what I needed! Thank you Massimo

Great work.

Not applicable

One last one thing please?!

If I want to:

a.) make a selection

b.) copy and paste the table object  into an Outlook email with text

c)  Send once a day to multiple recipients

is this possible? Can you please help?

Many thanks Massimo Grossi!

T

MVP
MVP

Sorry, I can't for outlook and email

Better if you open another thread, people don't look at already closed thread; in a new one, someone else will answer.