Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
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
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 ];
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 ];
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 🙂