10 Replies Latest reply: Jun 27, 2017 6:59 AM by Joanna Seldon RSS

    orders made on same date or next date Qlik Sense

    Joanna Seldon


      Hi

       

      I am trying to find out if orders from two different departments have been made on same day

       

      I have been trying to write the following in the load statement, but it does not seem to work

       

      please help

       

      if (("OrderDate" and  "Department" = 'Furniture') = ("OrderDate" AND "Department" = 'Kitchen'), 'Yes', 'No') as DualOrder

       

      but I also need to find out once this works how to see if someone has ordered from the Furniture department , but ordered the Same or Next Day from the kitchen department

       

      please help

        • Re: orders made on same date or next date Qlik Sense
          Sunny Talwar

          Would you be able to share few rows of sample data with the expected output?

            • Re: orders made on same date or next date Qlik Sense
              Joanna Seldon

              Hi

               

              CustomerID OrderDate      Department

              1                    01/01/2001    Kitchen

              1                    01/01/2001    Furniture

              1                    08/01/2001    Garden

              2                    01/01/2001    Kitchen

              2                    02/01/2001    Furniture

              2                    15/01/2001    Technology

              3                    01/01/2001    Kitchen

              3                    02/01/2001    Furniture

              3                    03/01/2001    Garden

               

               

               

              CustomerIDs 1 & 2 would be Yes, ....CustomerIDs 3 would be no

               

              please help

                • Re: orders made on same date or next date Qlik Sense
                  Sunny Talwar

                  And is this something you want to do in the script or a front end object?

                    • Re: orders made on same date or next date Qlik Sense
                      Joanna Seldon

                      hi

                       

                      I thought it would have been easier to do in the load and use set analysis and when I make the calculations

                       

                      count (Distinct {$<DualOrder={"Yes"}>} CustomerID)

                       

                      is there an easier way please?

                        • Re: orders made on same date or next date Qlik Sense
                          Sunny Talwar

                          Why do you have Customer2 as yes?

                           

                          Capture.PNG

                            • Re: orders made on same date or next date Qlik Sense
                              Joanna Seldon

                              Hi

                               

                              Yes...

                               

                              sorry there was an typo on the data sent - should have been

                               

                               

                              CustomerID OrderDate      Department

                              1                    01/01/2001    Kitchen

                              1                    01/01/2001    Furniture

                              1                    08/01/2001    Garden

                              2                    01/01/2001    Kitchen

                              2                    02/01/2001    Furniture

                              2                    15/01/2001    Technology

                              3                    01/01/2001    Kitchen

                              3                    05/01/2001    Furniture

                              3                    09/01/2001    Garden

                               

                              so

                               

                              ID 1 'Yes' is an example of a customer that orders from the Kitchen and Furniture department same day

                               

                              as ID 2 'Yes' is an example of a customer that orders from the Kitchen department but orders from the Furniture department the next day

                               

                              ID 3 is 'No'

                               

                              please help

                                • Re: orders made on same date or next date Qlik Sense
                                  Sunny Talwar

                                  May be this

                                   

                                  Table:

                                  LOAD *,

                                    Match(Department, 'Kitchen', 'Furniture', 'Garden') as DeptID;

                                  LOAD * INLINE [

                                  CustomerID, OrderDate,      Department

                                  1,                    01/01/2001,    Kitchen

                                  1,                    01/01/2001,    Furniture

                                  1,                    08/01/2001,    Garden

                                  2,                    01/01/2001,    Kitchen

                                  2,                    02/01/2001,    Furniture

                                  2,                    15/01/2001,    Technology

                                  3,                    01/01/2001,    Kitchen

                                  3,                    05/01/2001,    Furniture

                                  3,                    09/01/2001,    Garden

                                  ];

                                   

                                  Left Join

                                  LOAD CustomerID,

                                    MaxString(Flag) as Flag

                                  Group By CustomerID;

                                  LOAD CustomerID,

                                    If(CustomerID = Previous(CustomerID) and Match(DeptID, 1, 2) and (OrderDate = Previous(OrderDate) or OrderDate = Previous(OrderDate)+1), 'Yes', 'No') as Flag

                                  Resident Table

                                  Order By CustomerID, DeptID, OrderDate;

                                   

                                  Capture.PNG

                                    • Re: orders made on same date or next date Qlik Sense
                                      Joanna Seldon


                                      Hi

                                       

                                      this does work, but after reviewing the customers there is an issue

                                       

                                      if a customer orders from Kitchen on a day and then orders from Kitchen the next day... its bringing back these customers as yes

                                       

                                       

                                      for Example if we have customers like this 

                                      CustomerID OrderDate      Department

                                      1                    01/01/2001    Kitchen

                                      1                    01/01/2001    Kitchen

                                      1                    08/01/2001    Garden

                                      1                    12/01/2001    Kitchen

                                       

                                      2                    02/01/2001    Kitchen

                                      2                    03/01/2001    Furniture

                                      2                    08/01/2001    Technology

                                       

                                      3                    05/01/2001    Furniture

                                      3                    09/01/2001    Garden

                                       

                                      this example

                                       

                                      ID 1 is No

                                      ID 2 Is Yes

                                      ID 3 Is No

                                       

                                      please help

                                        • Re: orders made on same date or next date Qlik Sense
                                          Sunny Talwar

                                          May be try this

                                           

                                          Table:

                                          LOAD *,

                                            Match(Department, 'Kitchen', 'Furniture', 'Garden') as DeptID;

                                          LOAD * INLINE [

                                          CustomerID, OrderDate,      Department

                                          1,                    01/01/2001,    Kitchen

                                          1,                    01/01/2001,    Furniture

                                          1,                    08/01/2001,    Garden

                                          2,                    01/01/2001,    Kitchen

                                          2,                    02/01/2001,    Furniture

                                          2,                    15/01/2001,    Technology

                                          3,                    01/01/2001,    Kitchen

                                          3,                    05/01/2001,    Furniture

                                          3,                    09/01/2001,    Garden

                                          4,                    01/01/2001,    Kitchen

                                          4,                    01/01/2001,    Kitchen

                                          4,                    08/01/2001,    Garden

                                          4,                    12/01/2001,    Kitchen

                                          5,                    05/01/2001,    Furniture

                                          5,                    09/01/2001,    Garden

                                          ];

                                           

                                          Left Join

                                          LOAD CustomerID,

                                            MaxString(Flag) as Flag

                                          Group By CustomerID;

                                          LOAD CustomerID,

                                            If(CustomerID = Previous(CustomerID) and Match(DeptID, 1, 2) and DeptID <> Previous(DeptID) and

                                              (OrderDate = Previous(OrderDate) or OrderDate = Previous(OrderDate)+1), 'Yes', 'No') as Flag

                                          Resident Table

                                          Order By CustomerID, DeptID, OrderDate;