Derive New Field

[alvinford]

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

Thanks in Advance...

Regards,

Alvin.

[#2]

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

[alvinford]

Hi Dathu,

Thanks for your reply..

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

Regards,

Alvin.

[#4]

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

[alvinford]

Hi Dathu,

Thanks for your reply...

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

Regards,

Alvin.

[Alejandro_Hernández · Accepted solution]

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

map_from:

mapping

LOAD

    IDLOC,

    [Range From]

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Flag = 1;

map_to:

mapping

LOAD

    IDLOC,

    [Range To]

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Flag = 1;

data:

LOAD ID,

     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)

;

[alvinford]

Hi Alejandro,

Thanks for your reply...it's working fine..

Regards,

Alvin.