topic question about creating a new field while loading data in Qlik Learning Discussions

question about creating a new field while loading data

Anonymous — Tue, 17 Mar 2015 13:01:30 GMT

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 — Tue, 17 Mar 2015 13:51:37 GMT

See attached example.

Oleg_Troyansky — Tue, 17 Mar 2015 13:54:47 GMT

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 — Tue, 17 Mar 2015 13:59:09 GMT

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