Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gino2780
Contributor

convert numeric values into separate year fields(s)

Hello,

im looking for a script command that converts a product field (where the first digit of the value represents the production year info)

into separate year-fields (dummywise):

PRODUCT_NRConversion into2010201120132014
3084804290010
0460116401000
1440951840100
4136911610001
4301541200001

So the first digit of the product_nr 308480429 should be converted into a new field 2013

046011640 into 2010,

etc.


OR

a script command that generates a new year field based on the first digit of the product_nr:


PRODUCT_NRYEAR
3084804292013
0460116402010
1440951842011
4136911612014
4301541202014

Maybe you can answer both questions?

Anyways, thanks in advance!

1 Solution

Accepted Solutions
MVP
MVP

Re: convert numeric values into seperate year fields

May be this

LOAD PRODUCT_NR,

     Num#('201' & Left(PRODUCT_NR, 1)) as Year

8 Replies
MVP
MVP

Re: convert numeric values into seperate year fields

May be this

LOAD PRODUCT_NR,

     Num#('201' & Left(PRODUCT_NR, 1)) as Year

YoussefBelloum
Esteemed Contributor

Re: convert numeric values into seperate year fields

Hi,

try this:

Pick(Match(left(PRODUCT_NR,1),'0','1','2','3','4'),'2010','2010','2011','2012','2013','2014') as YEAR

gino2780
Contributor

Re: convert numeric values into separate year fields(s)

Hi Sunny,

always helpful, thanks again.

@Yousseff

also correct, although there was one 2010', obviously copy&paste, too much.

Thank you, too.

YoussefBelloum
Esteemed Contributor

Re: convert numeric values into separate year fields(s)

you're welcome

gino2780
Contributor

Re: convert numeric values into separate year fields(s)

With your help i could generate the 1st dummy-variant by myself (see qlik table scrrenshot below) with the follwoing code lines in the script:

pick(match(left(PRODUCT_NR,1),'0'),'1') as 2010,
pick(match(left(PRODUCT_NR,1),'1'),'1') as 2011,
pick(match(left(PRODUCT_NR,1),'3'),'1') as 2013,
pick(match(left(PRODUCT_NR,1),'4'),'1') as 2014;

YoussefBelloum
Esteemed Contributor

Re: convert numeric values into separate year fields(s)

if this is your expected output, you can create another year field as Year1, une a pivot table and pivot Year1

vvvvvvizard
Contributor III

Re: convert numeric values into separate year fields(s)

what happens when year 2015 comes , I prefer the left(field, 1) in my opinion

gino2780
Contributor

Re: convert numeric values into separate year fields(s)

True, 2 variables crossed with each other show the true values in the main diagonale and missing values in the secondary diagonale.