topic Re: Derive New Field in QlikView

Derive New Field

alvinford — Wed, 27 Nov 2013 13:36:24 GMT

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.

Re: Derive New Field

Wed, 27 Nov 2013 14:16:47 GMT

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

alvinford — Wed, 27 Nov 2013 14:50:13 GMT

Hi Dathu,

Thanks for your reply..

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

Regards,

Alvin.

Re: Derive New Field

Wed, 27 Nov 2013 14:58:08 GMT

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

Re: Derive New Field

alvinford — Wed, 27 Nov 2013 23:12:54 GMT

Hi Dathu,

Thanks for your reply...

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

Regards,

Alvin.

Re: Derive New Field

Alejandro_Hernández — Thu, 28 Nov 2013 02:28:20 GMT

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)

;

Re: Derive New Field

alvinford — Thu, 28 Nov 2013 12:48:29 GMT

Hi Alejandro,

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

Regards,

Alvin.