derive half year

[Chanty4u]

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

[MindaugasBacius · Accepted solution]

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?

[MindaugasBacius · Accepted solution]

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

[Chanty4u]

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

[sunny_talwar · Accepted solution]

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
];

[Chanty4u]

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?

[sunny_talwar]

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 🙂

[Chanty4u]

sorry my bad 🙂