orders made on same date or next date Qlik Sense

[joeybird]

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

[sunny_talwar]

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

[joeybird]

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

[sunny_talwar]

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

[joeybird]

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?

[sunny_talwar]

Why do you have Customer2 as yes?

[joeybird]

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

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

[joeybird]

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

[sunny_talwar · Accepted solution]

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;