topic Re: orders made on same date or next date Qlik Sense in App Development

orders made on same date or next date Qlik Sense

joeybird — Thu, 22 Jun 2017 10:21:31 GMT

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 — Thu, 22 Jun 2017 11:31:51 GMT

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

joeybird — Thu, 22 Jun 2017 11:38:15 GMT

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 — Thu, 22 Jun 2017 11:42:10 GMT

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

joeybird — Thu, 22 Jun 2017 11:47:20 GMT

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 — Thu, 22 Jun 2017 12:10:54 GMT

Why do you have Customer2 as yes?

Re: orders made on same date or next date Qlik Sense

joeybird — Thu, 22 Jun 2017 12:18:41 GMT

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

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 — Thu, 22 Jun 2017 12:43:26 GMT

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;

Re: orders made on same date or next date Qlik Sense

joeybird — Fri, 23 Jun 2017 07:49:47 GMT

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 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 — Fri, 23 Jun 2017 08:55:27 GMT

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, 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;

Re: orders made on same date or next date Qlik Sense

joeybird — Tue, 27 Jun 2017 10:59:00 GMT

what can I say...awesome star as always

working brilliantly

thanks again