12 Replies Latest reply: Feb 27, 2017 6:49 PM by Aar Kay RSS

    Doubt in Interval Match

    Gulshan Rohilla

      I have the below dataset :-

      Capture.PNG

       

      And I want this Output :-

      answer.PNG

       

      can anyone help me to do this....

        • Re: Doubt in Interval Match
          Aar Kay

          Try this:


          Table:

          Load F1,Date#(F2,'DD/MM/YYYY') as F2;

          LOAD * INLINE [

              F1, F2,F3

              1,12/12/2016,90

              1,01/01/2017,92

              2,01/02/2017,56

              2,22/02/2017,59

            

          ];

           

           

          Load

               F1,

               F2 as StartDate,

               If(F1=peek(F1),Date(peek(StartDate)-1,'DD/MM/YYYY'),Date(today())) as [End Date]

          Resident

                 Table

          Order By

                F1,F2 Desc;

          Drop Table

               Table;

            • Re: Doubt in Interval Match
              Gulshan Rohilla

              Thanks Aar Kay...

              Load F1,Date#(F2,'DD/MM/YYYY') as F2; // why we are using this preceeding Load


              and

              If(F1=peek(F1),Date(peek(StartDate)-1,'DD/MM/YYYY'),Date(today())) as [End Date]

              Can you explain me the above line



                • Re: Doubt in Interval Match
                  Aar Kay

                  preceeding load is just to tell qlikview that F2 is date field in 'DD/MM/YYYY' format for the sample data.

                   

                  If(F1=peek(F1) -this is if field value of F1 is equal to previous row value of F1,

                  then Date(peek(StartDate)-1,'DD/MM/YYYY') i.e take value of startdate "minus" 1day from  the previousrow

                  if field value of F1 is "not" equal to previous F1 then return today()

                    • Re: Doubt in Interval Match
                      Gulshan Rohilla

                      if I want  F3 Column also in the resultant table. for this what I need to do..

                      because simply Loading gives me an error that the F3 column is not found..

                        • Re: Doubt in Interval Match
                          Aar Kay

                          Try this:


                          Table:

                          Load *,Date#(F2,'DD/MM/YYYY') as F2;

                          LOAD * INLINE [

                              F1, F2,F3

                              1,12/12/2016,90

                              1,01/01/2017,92

                              2,01/02/2017,56

                              2,22/02/2017,59

                           

                          ];

                           

                           

                          Load

                               F1,

                               F2 as StartDate,

                               If(F1=peek(F1),Date(peek(StartDate)-1,'DD/MM/YYYY'),Date(today())) as [End Date],

                               F3

                          Resident

                                 Table

                          Order By

                                F1,F2 Desc;

                          Drop Table

                               Table;

                            • Re: Doubt in Interval Match
                              Gulshan Rohilla

                              But it gives me an error that F3 is not found

                                • Re: Doubt in Interval Match
                                  Aar Kay

                                  Did you change F1 to *  in the preceding load


                                  Table:

                                  Load *,Date#(F2,'DD/MM/YYYY') as F2;

                                  LOAD * INLINE [

                                      F1, F2,F3

                                      1,12/12/2016,90

                                      1,01/01/2017,92

                                      2,01/02/2017,56

                                      2,22/02/2017,59

                                   

                                  ];

                                   

                                   

                                  Load

                                      F1,

                                      F2 as StartDate,

                                      If(F1=peek(F1),Date(peek(StartDate)-1,'DD/MM/YYYY'),Date(today())) as [End Date],

                                      F3

                                  Resident

                                        Table

                                  Order By

                                        F1,F2 Desc;

                                  Drop Table

                                      Table;

                                    • Re: Doubt in Interval Match
                                      Gulshan Rohilla

                                      Below is the code which i tried to execute but gives error :-

                                      interval match.PNG

                                       

                                      Gives the following error :-

                                      Interval.PNG

                                        • Re: Doubt in Interval Match
                                          Aar Kay

                                          Sorry About that because of load * -F2 field is being loaded twice that is why it is giving you error

                                          instead of Load *,Date#(F2,'DD/MM/YYYY') as F2;

                                          Use this  Load F1,F3,Date#(F2,'DD/MM/YYYY') as F2;

                                            • Re: Doubt in Interval Match
                                              Gulshan Rohilla

                                              Thank You So Much Aar Kay. Now It is Working

                                                • Re: Doubt in Interval Match
                                                  Gulshan Rohilla

                                                  I have this dataset :-

                                                  AAA.PNG

                                                  In this I have to match the OrderDate with the previous table columns of start date and end date and Derive a new field Of Amount which is equals to Price * Qty {Price From the previous table}

                                                  and i want this output :-

                                                  BBB.PNG

                                                  can you please help me in this...

                                                    • Re: Doubt in Interval Match
                                                      Aar Kay

                                                      Using Intervalmatch like below:

                                                       

                                                       

                                                       

                                                      Table:

                                                      Load ProductID,F3,Date#(F2,'DD/MM/YYYY') as F2;

                                                      LOAD * INLINE [

                                                          ProductID, F2,F3

                                                          1,12/12/2016,90

                                                          1,01/01/2017,92

                                                          2,01/02/2017,56

                                                          2,22/02/2017,59

                                                       

                                                      ];

                                                       

                                                      Temp:

                                                      Load

                                                          ProductID,

                                                          F2 as StartDate,

                                                          If(ProductID=peek(ProductID),Date(peek(StartDate)-1,'DD/MM/YYYY'),Date(today())) as [End Date],

                                                          F3

                                                      Resident

                                                            Table

                                                      Order By

                                                            ProductID,F2 Desc;

                                                      Drop Table

                                                          Table;

                                                       

                                                       

                                                      A:

                                                      Load * inline [

                                                      OrderID,Order Date,ProductID,Salesrep,Qty

                                                      9123,15/12/2016,1,Suresh,6

                                                      6635,01/02/2017,2,Ramesh,4

                                                      5589,25/02/2017,2,Shubham,3

                                                      4698,02/02/2017,1,Ankit,2

                                                      ];

                                                       

                                                       

                                                      Inner Join(A)

                                                      IntervalMatch([Order Date],ProductID)

                                                      load

                                                      StartDate,

                                                      [End Date],

                                                      ProductID

                                                      Resident Temp;

                                                       

                                                       

                                                       

                                                       

                                                      Left Join (A)

                                                      load

                                                        StartDate,

                                                        [End Date],

                                                        ProductID,

                                                        F3

                                                      Resident Temp;

                                                      Drop Table Temp;

                                                      Final:

                                                      load

                                                      OrderID,[Order Date],ProductID,Salesrep,Qty,Qty*F3 as Amount

                                                      Resident A;

                                                      Drop TAble A;