Excel file format conversion.

[#1]

Below is the format of the  Excel file I am using and would like to change the format in the dashboard and I need help here please.

I want to make a bucket for Year ,month and then have the categories fall under that bucket.

Ex:

Region            Season-Year         

APAC              Summer-Jan - 2016            

EMEA             Spring-Jan - 2016           

NA                  Winter-Jan - 2016   

APAC              Summer-Feb - 2016            

EMEA             Spring-Feb - 2016           

NA                  Winter-Feb - 2016   

I want the above converted format to below format.I have to split it into Region,Year,Month,Season

Region         Year          Month     Season         

APAC            2016         Jan          Summer           

EMEA           2016         Jan           Spring           

NA                2016         Jan           Winter   

APAC            2016         Feb           Summer            

EMEA           2016         Feb            Spring           

NA                2016         Feb            Winter

Can we do this ?Thank much.

[sudeepkm · Accepted solution]

yes it can be done. pls see the attached solution.

[Frank_Hartmann]

try like this:

1:

LOAD * INLINE [

    Region, Season-Year

    APAC, Summer-Jan - 2016

    EMEA, Spring-Jan - 2016 

    NA, Winter-Jan - 2016  

    APAC, Summer-Feb - 2016 

    EMEA, Spring-Feb - 2016

    NA, Winter-Feb - 2016

];

Load Region,

     Subfield([Season-Year],'-',1) as Season,

     Subfield([Season-Year],'-',2) as Month,

     Subfield([Season-Year],'-',3) as Year

     Resident 1;

     DROP Table 1;

hope that helps

[#4]

Thanks much Suddep.