topic Re: Add Year Field to Table in QlikView

Add Year Field to Table

Anonymous — Wed, 07 Oct 2015 04:18:50 GMT

Hi i have a question,

I have load data from excel file named [Sales_1549-1608.xlsx]

NB: 1549 and 1608 is yearweek range

i've already transform the data and the result is like this:

I want to add one column called year.

the result will be like this:

WO RO AO QTY WEEK_ID Year

A D G 5 W01 2016

A D G 5 W02 2016

A D G 5 W03 2016

A D G 5 W04 2016

A D G 5 W05 2016

A D G 5 W06 2016

A D G 5 W07 2016

A D G 5 W08 2016

A D G 5 W49 2015

A D G 5 W50 2015

A D G 5 W51 2015

A D G 5 W52 2015

A D G 5 W53 2015

I also attach my qlikview file here

thanks

Re: Add Year Field to Table

sujeetsingh — Wed, 07 Oct 2015 04:29:41 GMT

What is the source of the Year field?

Well i have added it as per your output above.

Re: Add Year Field to Table

PrashantSangle — Wed, 07 Oct 2015 04:29:53 GMT

Hi,

Use filebaseName() or fileName() to retreive fileName

and using subfield() you can fetch only Year

Regards

Re: Add Year Field to Table

PrashantSangle — Wed, 07 Oct 2015 04:31:04 GMT

Re: Add Year Field to Table

SreeniJD — Wed, 07 Oct 2015 04:36:13 GMT

Hi Sujeet,

Just a small change in your script... Instead of <=49, we have to take <49 only as till 49 it has consider as Year 2015.

if( num(right(WEEK_ID,2))<49,2016,2015) as Year

Correct me If I'm wrong...

Re: Add Year Field to Table

Anonymous — Wed, 07 Oct 2015 04:46:40 GMT

Hi All,

Thanks for your idea.

It solve my problem perfectly.

Really glad to have u all.

Thanks

Re: Add Year Field to Table

Anonymous — Wed, 07 Oct 2015 04:47:58 GMT

Hi Max,

But how to identify that w49 till w53 is 2015 and w01-208 is 2016 ?