topic Re: Categorizing / Bucketing Data using Excel in QlikView

Categorizing / Bucketing Data using Excel

Mon, 20 Oct 2014 14:25:13 GMT

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
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,

Re: Categorizing / Bucketing Data using Excel

maxgro — Mon, 20 Oct 2014 14:46:34 GMT

maybe this; non matching event are excluded

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;

Re: Categorizing / Bucketing Data using Excel

Mon, 20 Oct 2014 18:49:49 GMT

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?

Re: Categorizing / Bucketing Data using Excel

maxgro — Mon, 20 Oct 2014 19:22:50 GMT

see attachment

Re: Categorizing / Bucketing Data using Excel

Tue, 21 Oct 2014 07:06:42 GMT

This is exactly what I needed! Thank you Massimo

Great work.

Re: Categorizing / Bucketing Data using Excel

Tue, 21 Oct 2014 07:10:45 GMT

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!

Re: Categorizing / Bucketing Data using Excel

maxgro — Tue, 21 Oct 2014 16:45:05 GMT

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.