Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How can I modify the usual script method for defining quarters (below) so that it works when the first quarter starts in March? (ie Jan and Feb are Q4?)
QuartersMap:
Mapping LOAD
RecNo() as Month,
'Q' & ceil(recno()/3) as Quarter
AutoGenerate 12;
Wondering if there is a quick workaround just by defining a map so that I list 1 = Q4, 2= Q4, 3=Q1, 4= Q1 etc, but I can't find the syntax.
Any help much appreciated
Hi Robert,
You can use the method mentioned in attached sample application.
Script:
QuartersMap:
Mapping
LOAD * INLINE
[Month, Qtr
1, Q4
2, Q4
3, Q1
4, Q1
5, Q1
6, Q2
7, Q2
8, Q2
9, Q3
10, Q3
11, Q3
12, Q4
];
MyTable:
LOAD RecNo() AS Mth,
ApplyMap('QuartersMap',RecNo()) AS Qtr
AutoGenerate(12);
The other programatic way is below:
MyTable:
LOAD RecNo() AS Mth,
Pick(Match(RecNo(),1,2,3,4,5,6,7,8,9,10,11,12), 4,4,1,1,1,2,2,2,3,3,3,4) AS Qtr
AutoGenerate(12);
Please revert back for any further query.
Cheers,
Dhananjay (DJ)
Well, if you like to use something like a lookup or mapping, you could probably do
'Q' & pick(recno(),4,4,4,1,1,1,2,2,2,3,3,3) as Quarter
or maybe without a lookup
'Q' & if( ceil(recno()/3)-1=0,4,ceil(recno()/3) ) as Quarter
Hope this helps,
Stefan
Hi Robert,
You can use the method mentioned in attached sample application.
Script:
QuartersMap:
Mapping
LOAD * INLINE
[Month, Qtr
1, Q4
2, Q4
3, Q1
4, Q1
5, Q1
6, Q2
7, Q2
8, Q2
9, Q3
10, Q3
11, Q3
12, Q4
];
MyTable:
LOAD RecNo() AS Mth,
ApplyMap('QuartersMap',RecNo()) AS Qtr
AutoGenerate(12);
The other programatic way is below:
MyTable:
LOAD RecNo() AS Mth,
Pick(Match(RecNo(),1,2,3,4,5,6,7,8,9,10,11,12), 4,4,1,1,1,2,2,2,3,3,3,4) AS Qtr
AutoGenerate(12);
Please revert back for any further query.
Cheers,
Dhananjay (DJ)
Many thanks for your help - Robert
Great – thanks for your held Dhannanjay
Thanks for your help Swuehl