6 Replies Latest reply: Nov 28, 2013 7:48 AM by alvinford ford

# Derive New Field

Dear All,

I have the below table.

 ID IDLOC Flag Range From Range To B1 B01 1 100 250 B2 B01 20 80 B5 B01 120 240 B6 B02 1 200 300 B8 B02 210 280 B9 B03 20 100 B11 B03 1 250 350 B12 B03 150 300

The logic i have to achieve is as follows:

I have to compare the "Range From" and "Range To" of same group  IDLOC with the IDLOC with Flag

For Ex1: The ID B2 has to be compared with ID B1 "Range From" and "Range To". If the "Range From" and "Range To" of ID B2 does not fall in between the "Range From" and "Range To" of ID B1 then Mark it as 1  else 0.

In the Same manner the B5 "Range From" and "Range To" has to be compared with B1 "Range From" and "Range To" and mark the Flag

Ex2: The ID B9 and B12 has to be compared with B11  "Range From" and "Range To" and mark the flag.

Finally need to achieve the New Field as Below

 ID IDLOC Flag Range From Range To New Field B1 B01 1 100 250 B2 B01 20 80 1 B5 B01 120 240 0 B6 B02 1 200 300 B8 B02 210 280 0 B9 B03 20 100 1 B11 B03 1 250 350 B12 B03 150 300 0

Regards,

Alvin.

• ###### Re: Derive New Field

you can achieve Ex:1  with Peek function (Compare previous value). Why you have Ex:2 only for B9, B11 & B12 ?

• ###### Re: Derive New Field

Hi Dathu,

I have to compare B9, B11 & B12 as they have the same IDLOC..

Regards,

Alvin.

• ###### Re: Derive New Field

I am asking why order got changed. why not  B9 with B11 and B12 with B11 ?

• ###### Re: Derive New Field

Hi Dathu,

The order may be different, not necessary that it will be in Order.

Regards,

Alvin.

• ###### Re: Derive New Field

If I understood the logic correctly, this should do what you need:

map_from:

mapping

IDLOC,

[Range From]

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Flag = 1;

map_to:

mapping

IDLOC,

[Range To]

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Flag = 1;

data:

IDLOC,

Flag,

if( applymap('map_from',IDLOC)>[Range From]

and applymap('map_to',IDLOC)>[Range From]

and applymap('map_from',IDLOC)>[Range To]

and applymap('map_to',IDLOC)>[Range To],1,0) as NewField,

[Range From],

[Range To]

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1)

;

• ###### Re: Derive New Field

Hi Alejandro,