Categorizing / Bucketing Data using Excel

[#1]

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 date	Ticker	Company Name	Event	EVENT TYPE
20/10/2014	SAPG.DE	SAP (Ordinary Share)	Q3 2014 SAP SE Earnings Call
20/10/2014	SAPG.DE	SAP (Ordinary Share)	Q3 2014 SAP SE Earnings Release
06/11/2014	SIEGn.DE	Siemens AG	Full Year 2014 Siemens AG Earnings Conference Call
06/11/2014	SIEGn.DE	Siemens AG	Full Year 2014 Siemens AG Earnings Release
11/11/2014	TW.L	Taylor Wimpey	Taylor Wimpey PLC Interim Management Statement Release
12/11/2014	HAYS.L	Hays plc	Hays PLC Annual Shareholders Meeting
12/11/2014	WBSV.VI	Wienerberger	Q3 2014 Wienerberger AG Earnings Release
09/12/2014	SIEGn.DE	Siemens AG	Siemens AG Capital Market Day "Vision 2020"
08/01/2015	HAYS.L	Hays plc	Hays 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 text	Event Type
Earnings Call	Earnings
Earnings Release	Earnings
Earnings Conference Call	Earnings
Interim Management Statement	Interim Management Statement
Capital Market Day	Capital Market Day

Can anyone please help on this?

Many thanks,

T

[maxgro]

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;

[#3]

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?

[maxgro · Accepted solution]

see attachment

[#5]

This is exactly what I needed! Thank you Massimo

Great work.

[#6]

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

[maxgro]

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.