Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

larisa_filonova
Contributor III

question about creating a new field while loading data

Hi,

I have a field Month in my source data. My task is to generate a new feld named Quarter based on the Month field.

So, while loading data I'd like to set the following condition:

- for values Jan, Feb, Mar generate value Q1 in the Quarter field

- for values Apr, May, Jun generate value Q2 in the Quarter field

- for values Jul, Aug, Sep generate value Q3 in the Quarter field

- for values Oct, Nov, Dec generate value Q4 in the Quarter field


Which operators should I use to implement it?


Thank you in advance,


Larisa

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: question about creating a new field while loading data

See attached example.


talk is cheap, supply exceeds demand
3 Replies
MVP & Luminary
MVP & Luminary

Re: question about creating a new field while loading data

See attached example.


talk is cheap, supply exceeds demand
MVP & Luminary
MVP & Luminary

Re: question about creating a new field while loading data

Hi Larisa,

First, you need to convert the Month field from alphanumeric string to a number. You can do it in a number of ways. One of them is this:

Match(Month, 'Jan', 'Feb', 'Mar', ..., 'Dec')

Then, use this commonly used calculation of the Quarter:

'Q' & ceil(MonthNumber/3) as Quarter.

cheers,

Oleg Troyansky

www.masterssummit.com - take you QlikView skills to the next level!

Re: question about creating a new field while loading data

Gysbert suggests

  'Q' & ceil(month(Month)/3) as Quarter

I would do it slightly more elaborate (to get numeric values):

   Dual('Q' & ceil(month(Month)/3), ceil(month(Month)/3)) as Quarter;

If you want sequential quarters, you can use QuarterName()

HIC

Community Browser