conditional dimension

[#1]

Hi Experts,

i have a dimension called Branchcode. it has the following values

Branchcode:

0001,

0002,

....

0501,

0601,

OP01,

DP02,

DP04,

CD03,

RF01, etc...

i have to use this dimension where it excludes 0501, 0601, code with text.

but it should have the codes that start with 'DP' . how can i do this.

i tried like this.

if(not Match(Branchcode,'0501','0601','0701')AND IsNum(Branchcode) and not match(left(Branchcode,2),'DP'), Branchcode)

but it is giving nulls.

Please help

[MK_QSL]

What's issue in using only below one?

Where not Match(Branchcode,'0501','0601','0701')

[#3]

no problem in that part.

if(not Match(Branchcode,'0501','0601','0701')AND IsNum(Branchcode) and

not match(left(Branchcode,2),'DP'), Branchcode)

the part highlighted in red is not working.

[sunny_talwar]

May be this:

If(not Match(Branchcode,'0501','0601','0701') and IsNum(Branchcode) or Wildmatch(Branchcode, '*DP*'), Branchcode)

[#5]

i tried this,

it is giving values for the first two part but not 'DP'

[MK_QSL]

Where (Not Match(Branchcode,'0501','0601','0701') and IsNum(Branchcode)) or WildMatch(Branchcode,'DP*')

[sunny_talwar · Accepted solution]

Check this out:

Table:

LOAD *,

  If(not Match(Branchcode,'0501','0601','0701') and IsNum(Branchcode) or Wildmatch(Branchcode, '*DP*'), Branchcode) as BranchcodeNew;

LOAD * Inline [

Branchcode

0001,

0002,

0501,

0601,

OP01,

DP02,

DP04,

CD03,

RF01

];

[#8]

its working sunny.

thanks a lot.

[#9]

thank you so much manish