7 Replies Latest reply: Mar 3, 2011 6:14 AM by Muncho Ts RSS

    intervalmatch?

      hello all.

      im new in QV please help.

      i have 2 table.

       

      table1:

      load * inline [

      id, pack_weight, type

      01, 0.2, express

      02, 0.9, normal

      03, 1.2, normal

      ....

      ];

      table2:

      load * inline [

      type, weight, cost

      normal, 05, 5

      normal, 1, 10

      normal, 1.5, 15

      ....

      express, 1, 15

      express, 2, 30

      express, 3, 35

      ....

      ];

       

      then i used intervalmatch to match weights.

       

      match:

      load * inline [

      start,end,weight

      0.1,0.5,0.5

      0.6,1,1

      1.1,1.5,1.5

      .....

      ];

      temp:

      intervalmatch(pack_weight) load start,end resident match;

      left join(temp) load * resident match;

      drop table match;

      left join(table1) load * resident temp;

      drop table temp;

       

      as u can see express weight start from 1kg. so its give me null under 0.5kg pack_weights.

      what should i do? help please.

      ps: i dont want to change or edit table2's values.

       

          • intervalmatch?
            Luis Laura

            Hi,

            I'm not understandind your case, please see image attached

            Null ok, do you must to use values for Cost or option check for omitted rows

            Good luck, Luis

              • intervalmatch?

                Hello Luis

                intervalmatch matchin table1 and temp tables.

                so table1 is like:

                id, type, pack_weight, weight, start, end

                01, express, 0.2, 0.5, 0.1, 0.5

                02, normal, 0.9, 1, 0.6, 1

                .....

                but in table2 there isn't 0.5kg in express type that's why its showing null.

                i need to match express type begins 1kg from temp table.

                Also there is some null inserted end of table after i used intervalmatch. And i want to use straight chart and sum(cost) but end of chart there is null and its add some costs.

                Sorry for my bad explain and English.

                 

            • intervalmatch?

              Table 2 & Temp both have a field named Weight. Because of this, when it does a join, it will match on that field name and only do the exact matches. This wilI lose the functionality of using an IntervalMatch. I would change the name in the Temp table to something like Category_Weight. It should resolve this.

              Thanks,

              Aline

                • intervalmatch?

                  Thank you Aline,

                  Well i lost few nulls. But its not solve my problem fully. anyway thanks again.

                    • intervalmatch?

                      I looked at this more closely. You are getting nulls because you are trying to do this with 2 dimensions Weight & Type. Notice that the nulls fall where there is not a definition for the combination of weight & type (ie: 0.2 falls into .5, but Express does not have anything defined for that). I would change your IntervalMatch statement to and Extended match.

                      Thanks,

                      Aline

                        • intervalmatch?

                          Hi Aline.

                          Thanks for your time. Problem is solved.

                           


                          fact:
                          load * inline [
                          id, type, weight
                          1047, normal, 0.4
                          2025, express, 0.3
                          1082, normal, 1.8
                          2093, express, 2.1
                          ];

                          weight_for_express:
                          load * inline [
                          start, end, weight_sort, type
                          0.1, 0.5, 0.5, express
                          0.6, 1, 1, express
                          1.1, 1.5, 1.5, express
                          1.6, 5, 5, express
                          ];

                          weight_for_normal:
                          load * inline [
                          start, end, weight_sort, type
                          0.1, 1, 1, normal
                          1.1, 2, 2, normal
                          2.1, 3, 3, normal
                          3.1, 4, 4, normal
                          ];

                          FactsWithWeights: // This table will be the final one.
                          INTERVALMATCH (weight, type) LOAD
                          start,
                          end,
                          type
                          RESIDENT weight_for_express;
                          LEFT JOIN LOAD *
                          RESIDENT fact;

                          DROP TABLE fact;
                          DROP TABLE weight_for_express;


                          Thanks to Miguel.