convert numeric values into separate year fields(s)

[gino2780]

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?

Anyways, thanks in advance!

[sunny_talwar · Accepted solution]

May be this

LOAD PRODUCT_NR,

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

[YoussefBelloum]

Hi,

try this:

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

[gino2780]

Hi Sunny,

always helpful, thanks again.

@Yousseff

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

Thank you, too.

[YoussefBelloum]

you're welcome

[gino2780]

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]

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

[vvvvvvizard]

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

[gino2780]

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