Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
;
you can achieve Ex:1 with Peek function (Compare previous value). Why you have Ex:2 only for B9, B11 & B12 ?
Hi Dathu,
Thanks for your reply..
I have to compare B9, B11 & B12 as they have the same IDLOC..
Regards,
Alvin.
I am asking why order got changed. why not B9 with B11 and B12 with B11 ?
Hi Dathu,
Thanks for your reply...
The order may be different, not necessary that it will be in Order.
Regards,
Alvin.
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)
;
Hi Alejandro,
Thanks for your reply...it's working fine..
Regards,
Alvin.