question about creating a new field while loading data

[Anonymous]

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

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[Oleg_Troyansky]

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!

[hic]

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