Text String with different 'fields' within.

[#1]

Hi,

I have an excel file with with data as follows:

Date	Period	Value
01/01/14	Apr15	10
01/01/14	Aug15	15
01/01/14	Q2 15	20
01/01/14	Q2 16	25
01/01/14	Summer15	30

The 'Period' column contains text but rather than load one field as text I would like to convert this into date format. I can do this if the text was all 'Apr15,Aug15...etc.'

The problem is the column can contain Months 'Apr15' , Quarters 'Q2 15' , Seasons 'Summer15' or Years 'Dec12 - Dec13'.

Is there any way in the script to convert this table field into different chronologically date fields?

e.g.

Period as PeriodMonth

Period as PeriodQuarter

Period as PeriodSeason

This would allow me to select different periods using different selectors and allow tables to be in chronological order rather than A-Z due to the text string at the moment.

Thanks,

Peter

[robert_mika]

LOAD Date,

     if(left(Period,1)='Q',left(Period,2)) as PeriodQuarter,

     if(left(Period,4)='Summ' or left(Period,4)='Wint' or left(Period,4)='Autu' or left(Period,4)='Spri',mid(Period,1,len(Period)-2)) as PeriodSeason,

     if(left(Period,3)='Sum' or left(Period,3)='Win' or left(Period,3)='Spr' or left(Period,3)='Aut' or left(Period,1)='Q',Null(),Left(Period,3)) as MonthPerdiod,

     Value

FROM

[151465.xlsx]

(ooxml, embedded labels, table is Sheet1);

[#3]

Hi Robert,

Thanks for your reply.

This does chop up the source text into different fields but they are still text and don't recognise the date element i.e. year. Therefore any of those created fields will still be ordered in text. Which is the original problem with just loading the one field 'Period'.

Basically I am looking to separate the information in the 'Period' Field into chronologically ordered separate components. I.e. recognise Aug-15, Jul-15, Q2 15, Q1 16, Summer15, Winter15 as date values &split them out into different fields.

Not sure if this is possible.

Thanks,

Peter

[robert_mika]

What would be the date value for Q1 16 or Summer15?