Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

joeybird
Contributor II

orders made on same date or next date Qlik Sense


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

1 Solution

Accepted Solutions
MVP
MVP

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

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;

10 Replies
MVP
MVP

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

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

joeybird
Contributor II

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

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

MVP
MVP

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

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

joeybird
Contributor II

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

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?

MVP
MVP

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

Why do you have Customer2 as yes?

Capture.PNG

joeybird
Contributor II

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

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

MVP
MVP

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

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

joeybird
Contributor II

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


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

MVP
MVP

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

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;

Community Browser