Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Categorizing / Bucketing Data using Excel

see attachment

6 Replies
MVP
MVP

Re: Categorizing / Bucketing Data using Excel

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

Re: Categorizing / Bucketing Data using Excel

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

Re: Categorizing / Bucketing Data using Excel

see attachment

Not applicable

Re: Categorizing / Bucketing Data using Excel

This is exactly what I needed! Thank you Massimo

Great work.

Not applicable

Re: Categorizing / Bucketing Data using Excel

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

Re: Categorizing / Bucketing Data using Excel

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.

Community Browser