5 Replies Latest reply: Apr 22, 2015 4:49 AM by bobbyraj santhiogu RSS

    Look for value in range

      Hi all.

       

      I atach an excel with example of value and expected table.

       

      Basically i have a table with a date. With that date and id i need look for a value in other table, but the other table has from - to no exaclty the same date.

       

      I atach to, expectec table.

       

      Thanks in advance.

        • Re: Look for value in range
          Martin Bacul�k

          Hi Federico,

           

          I am not sure, whether I cought your point or not, but looks like you have to use ex. IntervalMatch() function with some join.

           

          Henric created very nice topic about such issue, link: IntervalMatch and Slowly Changing Dimensions

           

          Hope would help

           

          M

          • Re: Look for value in range
            Jonathan Dienst

            This sound like an interval matching problem. Check out interval matching in the reference manual or search here for "interval match"

             

            I suggest that you post a sample of your qvw document for more detailed help.

             

            HTH

            Jonathan

              • Re: Look for value in range

                I do it.. but i think will be better my script. This script give expect result. can you give me an advise?

                 

                table:
                LOAD DateStart,
                idCustomer,
                DateStart&'|'& idCustomer as key_date_customer
                FROM
                INFO.xlsx
                (
                ooxml, embedded labels, table is Sheet1);

                Intervals:
                LOAD Datefrom,
                Dateto,
                Datefrom& '|'&Dateto as _key,
                idCustomer as idCustomer_aux,
                QUANTITY
                FROM
                INFO.xlsx
                (
                ooxml, embedded labels, table is Sheet2);

                IntervalMatch_aux:
                IntervalMatch (DateStart)
                Load distinct Datefrom, Dateto resident Intervals;

                left join (Intervals)
                LOAD
                Datefrom& '|'&Dateto as _key,
                DateStart Resident IntervalMatch_aux; drop Table IntervalMatch_aux;
                drop fields  Datefrom, Dateto, _key from Intervals;

                left join(table)
                LOAD
                num(DateStart)&'|'& idCustomer_aux as key_date_customer,
                QUANTITY
                Resident Intervals; DROP Table Intervals;
                drop Field key_date_customer from table;

              • Re: Look for value in range
                Martin Bacul�k

                table:
                LOAD DateStart,
                idCustomer
                FROM
                INFO.xlsx
                (
                ooxml, embedded labels, table is Sheet1);

                Intervals:
                LOAD Datefrom,
                Dateto,
                idCustomer,
                QUANTITY
                FROM
                INFO.xlsx
                (
                ooxml, embedded labels, table is Sheet2);

                IntervalMatch_aux:
                IntervalMatch (DateStart, idCustomer)
                Load distinct Datefrom, Dateto, idCustomer resident Intervals;

                New:
                LOAD *
                RESIDENT Intervals;
                drop table table;


                left join(New)
                LOAD *
                Resident Intervals;
                drop table intervals;

                 

                Sorry, but I have no QlikView desktop right here, created on the fly.

                 

                 

                M

                • Re: Look for value in range
                  bobbyraj santhiogu

                  Hi,

                  You should use the fonction 'Intervalmatch' in your case:

                   

                  TABLE1:
                  LOAD DateStart,
                  idCustomer
                  FROM
                  [.\INFO.xlsx]
                  (
                  ooxml, embedded labels, table is Sheet1);

                  TABLE2:
                  LOAD Datefrom,
                  Dateto,
                  idCustomer,
                  QUANTITY
                  FROM
                  [.\INFO.xlsx]
                  (
                  ooxml, embedded labels, table is Sheet2);

                  LEFT JOIN(TABLE2)
                  IntervalMatch(DateStart)
                  LOAD Datefrom,
                  Dateto
                  RESIDENT TABLE2;

                  LEFT JOIN(TABLE2)
                  LOAD *
                  RESIDENT
                  TABLE1;

                  DROP TABLE TABLE1;