3 Replies Latest reply: Mar 22, 2014 8:38 PM by Marco Wedel RSS

    Interval between

    snehal mejari

      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

        • Re: Interval between
          Coen Donders

          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
            snehal mejari

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