Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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

Hello,

Suppose the following:

  1. We already have a field containing a large amount of dates in the example 2002-04-03, 2005-12-19, 2010-10-09 etc.
  2. What I want is to create a custom field containing elements such as 2002-Q1, 2003-Q4, 2009-Q2, 2011-Q3 etc.
  3. 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

1 Solution

Accepted Solutions
erich_shiino
Honored Contributor

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

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

5 Replies
erich_shiino
Honored Contributor

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

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

Not applicable

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

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

Not applicable

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

That worked like a charm, thank you :-)

Not applicable

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

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

- Sridhar

Not applicable

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

Dont be! The more the merrier :-) I appreciate all the help I can get!