Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

alvinford
New Contributor III

Derive New Field

Dear All,

I have the below table.

IDIDLOCFlagRange FromRange To
B1B011100250
B2B01 2080
B5B01 120240
B6B021200300
B8B02 210280
B9B0320100
B11B03                         1250350
B12B03 150300

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

IDIDLOCFlagRange FromRange ToNew Field
B1B011100250
B2B01 20801
B5B01 1202400
B6B021200300
B8B02 2102800
B9B03 201001
B11B03                         1250350
B12B03 1503000

Thanks in Advance...

Regards,

Alvin.

1 Solution

Accepted Solutions
Employee
Employee

Re: Derive New Field

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)

;

6 Replies
Not applicable

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 ?

alvinford
New Contributor III

Re: Derive New Field

Hi Dathu,

Thanks for your reply..

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

Regards,

Alvin.

Not applicable

Re: Derive New Field

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

alvinford
New Contributor III

Re: Derive New Field

Hi Dathu,

Thanks for your reply...

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

Regards,

Alvin.

Employee
Employee

Re: Derive New Field

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
New Contributor III

Re: Derive New Field

Hi Alejandro,

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

Regards,

Alvin.

Community Browser