How to pick the quarter from a field containg both text and quarter

[dandaanilreddy]

Hi All,

I have a field in which i have values like below. I am trying to derive the quarter from these values but the position of the quarter will be change everytime. The subfield function is not working to pick the quarter. I want to derive the quarter every time irrespective of the quarter position. Please can someone help me on this?

ABCD.EFGH.CYCLE0.2019Q2.PRODUCT10706

ABCD.EFGH.CYCLE0.XXXX.2019Q2.IA1.ABCa1

Thanks,

[jt422605]

=mid(Field,index(Field,'Q',1),2)

[sunny_talwar]

Like this in the script

MappingTable:
Mapping
LOAD QuarterName,
	 '/' & QuarterName & '\';
LOAD * INLINE [
    QuarterName
    Q1
    Q2
    Q3
    Q4
];

Table:
LOAD *,
	 TextBetween(MapSubString('MappingTable', Field), '/', '\') as Quarter;
LOAD * INLINE [
    Field
    ABCD.EFGH.CYCLE0.2019Q2.PRODUCT10706
    ABCD.EFGH.CYCLE0.XXXX.2019Q2.IA1.ABCa1
    ABQCD.EFGH.CYCLE0.XXXX.2019Q3.IA1.ABCa1
];

[dandaanilreddy]

Hi 

It's picking Q2 only. I want 2018Q2 complete quarter with year.

Thanks,

[jt422605]

Try this -  

mid(Field,index(Field,
pick(wildmatch(Field,'*Q1*','*Q2*','*Q3*','*Q4*'),'Q','Q','Q','Q'),1)
-4,6)