5 Replies Latest reply: Jun 6, 2018 9:29 AM by Giacinto Abbruzzese

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_NR Conversion into 2010 2011 2013 2014 308480429 → 0 0 1 0 046011640 → 1 0 0 0 144095184 → 0 1 0 0 413691161 → 0 0 0 1 430154120 → 0 0 0 1

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_NR YEAR 308480429 2013 046011640 2010 144095184 2011 413691161 2014 430154120 2014

Maybe you can answer both questions?

• Re: convert numeric values into seperate year fields

May be this

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

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

Hi Sunny,

@Yousseff

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

Thank you, too.

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

you're welcome

• 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

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

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

• 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;

• 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

• 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.