How to create field containg these values?

[Anonymous]

Dear all,

i have two coloumens project name and transaction class in this for pericular project name there are one or many trans class ie like NRE,PROEXPENCE,BU-LEVEL,GRASSSET,etc.

in this we create one more field that satisify this condation.

if(transaction class=NRE and PROEXPENCE,'p2c')

if(transaction class="BU-LEVEL" and PROEXPENCE,'p2c')

if(transaction class="BU-LEVEL" and NRE,'p2c')

if (transaction class='NRE',NRE')

if (transaction class='BU-LEVEL',BU-LEVEL)

if (transaction class=PROEXPENCE',PROEXPENCE)

output:

project name    transation class     chngetransation class

altis                     NRE                            P2C

altis                   BU-LEVEL                     P2C

altis                   PROEXPENCE               P2C

etios                  NRE                              NRE

wanger               PROEXPENCE              PROEXPENCE             

audi                   NRE                              P2C

audi                   PROEXPENCE               P2C

swis                  EXPENCIVE                  -

hundi                 BU-LEVEL                    BU-LEVEL

thanks

arun

[swuehl · Accepted solution]

Maybe like this?

I2:

LOAD * INLINE [

project name,    transaction class

altis,                     NRE

altis,                   BU-LEVEL

altis,                   PROEXPENCE

etios,                  NRE

wanger,               PROEXPENCE

audi,                   NRE

audi,                   PROEXPENCE

swis,                  EXPENCIVE

hundi,                 BU-LEVEL

];

Left join LOAD

[project name],

pick(

wildmatch(concat([transaction class],','),'*NRE*PROEXPENCE*','*BU-LEVEL*PROEXPENCE*','*BU-LEVEL*NRE*','*NRE*','*BU-LEVEL*','*PROEXPENCE*')

,'p2c','p2c','p2c','NRE','BU-LEVEL','PROEXPENCE') as [new class]

resident I2 Group by [project name];

hope this helps,

Stefan

[Anonymous]

HI stefan,thanks for replay.

its correct answer.

[Anonymous]

hi,

OpxProjectName	TransactionClass	TransactionClassMap
Altius-1	Depreciation
Altius-1	External Capital
Altius-1	Internal Capital
Altius-1	NRE	NRE
Altius-1	NRE - FT
Altius-1	Outside Services
Altius-1	Outsourced Services
Altius-1	Proto Expense	Proto
Altius-1	TRAVEL
Bran (Fortius   G+, G- and Fortius F-)	Depreciation
Bran (Fortius   G+, G- and Fortius F-)	Internal Capital
Bran (Fortius   G+, G- and Fortius F-)	NRE	NRE
Bran (Fortius   G+, G- and Fortius F-)	Proto Expense	Proto
CTP   Outsourcing (Non-NPI Project)	NRE	NRE
EABU Finance   - Occupancy related	Unclassified Expense
Nano (1/2   Tiny)	BU-level Owned Budget	P2C
Nano (1/2   Tiny)	Depreciation
Nano (1/2   Tiny)	External Capital
Nano (1/2   Tiny)	Internal Capital
Nano (1/2   Tiny)	NRE	P2C
Nano (1/2   Tiny)	Proto Expense	P2C

i want to create one more field ie transactionclassmap that contains these output values.we have projectname and transactionclass note:combination of BU-LEVEL OWNED with proto expence or NRE is 'P2C'.Please tell me how to solve this in backend.