topic Re: Month and Year in QlikView

Month and Year

Mon, 08 Dec 2014 11:58:19 GMT

Hi,

I have a file name as such 'DetailPL_FY1415.xlsx'. The file contains two fields in particular labelled at FY and Month.

FY contains the value 2014/15

Month contains the values Jan, Feb, March etc

The financial year starts in August and ends in September.

How do I create a MonthYear field? I,e Dec-2014, Jan-2015 etc...? Can I still use the filename?

Thanks

Re: Month and Year

anbu1984 — Mon, 08 Dec 2014 12:07:42 GMT

LOAD *,Month & '-' & Subfield(FY,'/',1) As MonthYear Inline [

FY,Month

2014/15,Jan

2014/15,Feb ];

Re: Month and Year

Mon, 08 Dec 2014 12:12:29 GMT

Month & '-' & LEFT(FY,4) AS MonthYear

Re: Month and Year

its_anandrjs — Mon, 08 Dec 2014 12:13:11 GMT

Hi,

You can try

Load Month&'-'&Left(FY,4) as MonthYear;

Load * Inline

[

FY,Month

2014/15,Sep

2014/15,Oct

2014/15,Nov

2014/15,Dec

];

Month&'-'& Year( Date(Left(FY,4),'YYYY) ) AS MonthYear;

Regards

Anand

Re: Month and Year

Mon, 08 Dec 2014 12:13:43 GMT

Hi Anbu

That works fine, but again, the issue is Aug, Sep, Oct, Nov, Dec should be 2014 and Jan, Feb, Mar till Jul need to be 2015.

How do I make this split?

Re: Month and Year

anbu1984 — Mon, 08 Dec 2014 12:19:23 GMT

LOAD *,Month & '-' & If(Match(Month,'Aug','Sep','Oct','Nov','Dec'),Subfield(FY,'/',1),'20'&Subfield(FY,'/',2)) As MonthYear Inline [

FY,Month

2014/15,Jan

2014/15,Feb

2014/15,Mar

2014/15,Apr

2014/15,May

2014/15,Jun

2014/15,Jul

2014/15,Aug

2014/15,Sep

2014/15,Oct

2014/15,Nov

2014/15,Dec

];

Re: Month and Year

Mon, 08 Dec 2014 12:31:13 GMT

Thank you all