topic Interval between in QlikView

Interval between

Wed, 19 Mar 2014 19:40:21 GMT

I have two tables as shown for eg:-

minbandwidth	maxbandwidth	VC3	VC4	VC4c4	VC4c16	VC4c64	VC11	VC12
40	56	2						2
63	74			5				1
70	80				6			3
75	78	5	2

OBJECTNAME	bandwidth	VC3	VC4	VC4c4	VC4c16	VC4c64	VC1	VC12	Remarks
545	73	-	-	-	-	-		1	Matching
454	50	2	-	-	-	-	-	1
566	656	5	4

1)now if im selecting bandwidth 73(HIghlighted) from table2 for objectname 545 it has VC12(count 1)and if i'm seing it in table1 bandwidth 73 comes under 2 intervals (63-74) and(70-80) but VC12(count1) falls under (63-74) interval so myremark column wud be updated as Matching

Please suggest as soon as possible

Re: Interval between

cgdonders — Wed, 19 Mar 2014 20:42:36 GMT

maybe you are looking for something like this:

// load min and max ranges with data. Use qualify to create table specific fieldnames.

QUALIFY*;

table1:

LOAD * INLINE [

minbandwidth, maxbandwidth , VC3 , VC4 , VC4c4 , VC4c16 , VC4c64 , VC11 , VC12

40 , 56 ,2 , , , , , ,2

63 , 74 , , ,5 , , , ,1

70 , 80 , , , ,6 , , ,3

75 , 78 ,5 ,2 , , , , ,

];

UNQUALIFY*;

// load objectname data in a temporary table

table2_temp:

LOAD * INLINE [

OBJECTNAME , bandwidth , VC3 , VC4 , VC4c4 , VC4c16 , VC4c64 , VC11 ,VC12

545 ,73 ,- ,- ,- ,- ,- , ,1

454 ,50 ,2 ,- ,- ,- ,- , ,1

566 ,656 ,5 ,4 ,

];

// load interval match

Left join(table2_temp)

Intervalmatch(bandwidth)

LOAD [table1.minbandwidth], [table1.maxbandwidth]

RESIDENT table1;

// no synthetic key

Left join (table2_temp)

LOAD * resident table1;

drop table table1;

// show remarks if there is a match

Matches:

LOAD

OBJECTNAME

, bandwidth

, VC3

, VC4

, VC4c4

, VC4c16

, VC4c64

, VC11

, VC12

, IF(VC12=table1.VC12, 'Matching') AS Remarks

Resident table2_temp

WHERE VC12=table1.VC12;

// load object name data again

Facts:

LOAD * INLINE [

OBJECTNAME , bandwidth , VC3 , VC4 , VC4c4 , VC4c16 , VC4c64 , VC11 ,VC12

545 ,73 ,- ,- ,- ,- ,- , ,1

454 ,50 ,2 ,- ,- ,- ,- , ,1

566 ,656 ,5 ,4 ,

];

// left join remarks data

LEFT JOIN (Facts)

LOAD

OBJECTNAME

, Remarks

Resident Matches;

drop table Matches;

drop table table2_temp;

Re: Interval between

Thu, 20 Mar 2014 09:00:56 GMT

but my bandwidth column mentioned in table2 is cuming from sum anoder table

Re: Interval between

MarcoWedel — Sun, 23 Mar 2014 00:38:41 GMT

Hi Snehal,

I'm afraid your requirement still is not clear. Could you please add some info?

thanks

regards

Marco