Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Excel file format conversion.

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.

1 Solution

Accepted Solutions
sudeepkm
Valued Contributor III

Re: Excel file format conversion.

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

View solution in original post

3 Replies
sudeepkm
Valued Contributor III

Re: Excel file format conversion.

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

View solution in original post

Frank_Hartmann
Honored Contributor II

Re: Excel file format conversion.

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

Not applicable

Re: Excel file format conversion.

Thanks much Suddep.