topic Re: derive half year in QlikView

derive half year

Chanty4u — Sat, 16 Nov 2024 21:32:14 GMT

Hi all,

@sunny_talwar

I have one filed from database that is Quarter from this i need to derive year and half year

Quarter

2018Q1

2018Q2

2018Q3

2018Q4

2019Q1

so i have created like this . is there any other way to derive half year?

Q1 and Q2 as H1 first half year

Q3 and Q4 as H2 second half year

script:

load

Quarter,
left(Quarter,4) as Year,
IF(Right(Quarter,1)= 1, LEFT(Quarter,4) & 'H1',
IF(Right(Quarter,1)= 2, LEFT(Quarter,4) & 'H1',
IF(Right(Quarter,1)= 3, LEFT(Quarter,4) & 'H2',
IF(Right(Quarter,1)= 4, LEFT(Quarter,4) & 'H2',Quarter)))) as Half_Year;

Note: My quarters are [calendar year only]

jan,feb,mar Q1

aprl,may,jun Q2

jul,aug,sep Q3

oct,nov,dec Q4..

Thanks

Re: derive half year

MindaugasBacius — Fri, 25 Jan 2019 10:55:25 GMT

You can use also like this:
If(Right(Quarter, 1) < 3, LEFT(Quarter,4) & 'H1', LEFT(Quarter,4) & 'H2') as Half_Year
or
If(Match(Right(Quarter, 1), 1, 2), LEFT(Quarter,4) & 'H1', LEFT(Quarter,4) & 'H2') as Half_Year

Are you looking for this solutions?

Re: derive half year

MindaugasBacius — Fri, 25 Jan 2019 11:05:49 GMT

Besides I have forgotten to mention mapping option:
map_half_year:
Mapping LOAD * INLINE [
F1, F2
1, H1
2, H2
3, H3
4, H4
];

In your Calendar table write this expression:
LEFT(Quarter,4) & ApplyMap('map_half_year', Right(Quarter, 1)) as Half_Year

Re: derive half year

Chanty4u — Fri, 25 Jan 2019 11:05:55 GMT

if i use the above two conditions im getting 'H2' also as a value in the listbox.

Re: derive half year

sunny_talwar — Fri, 25 Jan 2019 12:10:42 GMT

May be try something like this

MappingTable:
Mapping
LOAD * INLINE [
    Quarters, HalfYears
    Q1, H1
    Q2, H1
    Q3, H2
    Q4, H2
];

Fact:
LOAD *,
	 MapSubString('MappingTable', Quarter) as Half_Year;
LOAD * INLINE [
    Quarter
    2018Q1
    2018Q2
    2018Q3
    2018Q4
    2019Q1
];

Re: derive half year

Chanty4u — Fri, 25 Jan 2019 12:24:47 GMT

thank you sunny. but my data will increase so many quarters will come.. is inline will work? again i need to write the quarter values right?

Re: derive half year

sunny_talwar — Fri, 25 Jan 2019 12:42:10 GMT

Are you saying that you might have quarters come in as Q5, Q6, Q7, Q8... and so on? 🙂

If not, then inline can stay the same 🙂

Re: derive half year

Chanty4u — Fri, 25 Jan 2019 12:50:19 GMT

sorry my bad 🙂