Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Interval between

I have two tables as shown for eg:-

minbandwidthmaxbandwidthVC3VC4VC4c4VC4c16VC4c64VC11VC12
405622
6374 5 1
7080 6 3
757852
OBJECTNAMEbandwidthVC3VC4VC4c4VC4c16VC4c64VC1VC12Remarks
54573----- 1Matching
454502-----1
56665654

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

3 Replies
cgdonders
Contributor

Re: Interval between

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;

Not applicable

Re: Interval between

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

Re: Interval between

Hi Snehal,

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

thanks

regards

Marco

Community Browser