topic Re: How to: create custom field that selects specific elements in other fields in QlikView

How to: create custom field that selects specific elements in other fields

Tue, 31 May 2011 14:13:08 GMT

Hello,

Suppose the following:

We already have a field containing a large amount of dates in the example 2002-04-03, 2005-12-19, 2010-10-09 etc.
What I want is to create a custom field containing elements such as 2002-Q1, 2003-Q4, 2009-Q2, 2011-Q3 etc.
When selecting the fields in #2 above I would like that all the corresponding elements in the field mentioned in #1 to be selected

Help appreciated 🙂

best regards

How to: create custom field that selects specific elements in other fields

erichshiino — Tue, 31 May 2011 15:03:06 GMT

is it already a date or just a string?

if it`s only a string you need to include a field like this:

I will assume that you field is called DATE

Load (etc ... ),

LEFT( DATE,4) & '-Q'& CEIL( NUM( MONTH( date#(DATE, 'YYYY-MM-DD') )) /3) AS YearQuarter

from / resident (etc...)

If it`s already a date, it can be just:

year(DATE) & '-Q'& CEIL( NUM(MONTH(DATE))/3) AS YearQuarter

Hope it helps,

Erich

Re: How to: create custom field that selects specific elements in other fields

Tue, 31 May 2011 15:07:41 GMT

Hi, If i understood your question properly. Qlikview will always work as per you are execting, if you have proper association between the table.

Take for example you have date as folows 2002-04-03, 2005-12-19, 2010-10-09, if you create year out of it and selected 2002, it would always select 2002-04-03.

Hope i have understood your question, if not correct me by rephrasing your question.

- Sridhar

Re: How to: create custom field that selects specific elements in other fields

Tue, 31 May 2011 15:24:49 GMT

That worked like a charm, thank you 🙂

Re: How to: create custom field that selects specific elements in other fields

Wed, 01 Jun 2011 02:42:14 GMT

Sorry, I have misunderstood your question and my apologies for wrong answer.

- Sridhar

Re: How to: create custom field that selects specific elements in other fields

Wed, 01 Jun 2011 08:28:06 GMT

Dont be! The more the merrier 🙂 I appreciate all the help I can get!