Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dandaanilreddy
Partner - Creator III
Partner - Creator III

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

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,

 

4 Replies
jt422605
Contributor III
Contributor III

=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
Partner - Creator III
Partner - Creator III
Author

Hi 

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

 

Thanks,

 

jt422605
Contributor III
Contributor III

Try this -  

 

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