Required to change the format of the Entries in the field

[Sreekanth07]

Field called Fiscal Period, in that the entries are like 1, 2,3, 4 (quarter numbers) i want to replace those numbers as Q1 CY23,

Q2 CY23 etc. and also CY23 should automatically change to CY24 when year changes, 

could anyone help how to achieve this.

thanks.

[martinpohl]

Use this:

// set year to actual year

let year = right(year(today()),2);

quarter_map:

mapping load 

rowno() as Quarter_input

'Q'&rowno()&' CY'&$(year) as Quarter_output

autogenerate 4;

then, when load your datas, change load line from your field quarter

load

......

applymap('quarter_map',quarter) as quarter,

....

from your source;

Regards

[Ahidhar]

try this on the Date Field

Tab:
load * Inline
[Date
1/11/2023
25/11/2023
3/1/2024
4/1/2024
];

tab1:
load *,
'Q'&ceil(Month(Date)/3)&' '&'CY'&year(Date) as Quarter
resident Tab;
drop table Tab;