Compare current week order snapshot to previous weeks

[Shank]

Hi all,

i am new to alteryx and i need your help to fix this problem

i need to compare the current week data(snapshot) to previous week and Check where there are any change to the existing order below is the same data.

per below data order 1234 present in week 23 but not in current week i need status a deleted order in remarks

Comparison rules

1. Compare current week line item with all other week

2.if order is new current week show status as new

3.if order exists in old week but does not exists in new week then status as deleted

4.track change in delivery date.

5.track change in quantity

i have provided how output should be but i am OK with better solution.

many thanks in advance for your help

Shaz

Material	Material Type	Order Number	Supplier	Delivery Date	Delivery Quantity	snapshot Date	Week
1	Raw	1234	ABC	2018-07-02	15	6/6/2018 12:29	23
2	Finished	1235	ABC	2018-07-02	11	6/6/2018 12:29	23
3	Finished	1236	ABC	2018-07-02	20	6/6/2018 12:29	23
4	Finished	1237	ABC	2018-07-02	13	6/6/2018 12:29	23
1	Raw	1234	ABC	2018-07-02	10	6/12/2018 12:29	24
2	Finished	1235	ABC	2018-07-02	11	6/12/2018 12:29	24
3	Finished	1236	ABC	2018-07-02	12	6/12/2018 12:29	24
4	Finished	1237	ABC	2018-07-02	13	6/12/2018 12:29	24
5	Finished	1238	ABC	2018-07-02	14	6/12/2018 12:29	24
6	Finished	1239	ABC	2018-07-02	15	6/12/2018 12:29	24
3	Finished	1236	ABC	2018-07-02	12	6/12/2018 12:29	24
4	Finished	1237	ABC	2018-07-02	13	6/17/2018 12:29	25
5	Finished	1238	ABC	2018-07-02	14	6/17/2018 12:29	25
6	Finished	1239	ABC	2018-07-02	15	6/17/2018 12:29	25
7	Finished	1240	ABC	2018-07-02	22	6/17/2018 12:29	25
8	Finished	1241	ABC	2018-07-02	24	6/17/2018 12:29	25

Output

\

Material	Material Type	Order Number	Supplier	Delivery Date	Delivery Quantity	snapshot Date	Current Week	Order previous Week	Snapshot Date	Previous Week No	Privious Week Delivery Date	Previous Week Quantity	Remarks
4	Finished	1237	ABC	7/12/2018	13	6/17/2018 12:29	25	1237	6/12/2018 12:29	24	2018-07-02	10
4	Finished	1237	ABC	7/12/2018	13	6/17/2018 12:29	25	1237	6/6/2018 12:29	23	2018-07-02	13
1	Finished	1234	ABC	2018-07-02	15	6/6/2018 12:29	23	1234	6/6/2018 12:29	23	2018-07-02	15	Deleted from Current Week
8	Finished	1241	ABC	2018-07-02	24	6/17/2018 12:29	25						New Order

[NicoleJohnson · Accepted solution]

In this case, I believe the Multi-Row tool is going to help you resolve. 

If you filter your data by Order Number, Material, and Week, you can then group by Order Number and/or Material in the Multi-Row tool and check to see the prior week's value. For example, to find out if an order number existed in a prior week, I would configure the tool as follows:

1. Create New Field "Order Previous Week"

2. Set the Values for Rows that don't exist to "NULL"

3. Group by Order Number

4. Expression = [Row-1:Order Number]

This will pick up the prior row's order number, grouping by each order. If there is no prior week for that order number (because it's the first week) it will show NULL, indicating a new order that week. With a series of these multi-row formulas, you can check all your required fields for existing values and/or changes from prior week. You can also look ahead to the next week as well for the case where you want to identify a Deleted order - if the snapshot date isn't equal to the current week and the Row+1 Order Number is NULL because it does not exist, then you know that order was deleted in the current week.

I've attached a workflow that should get you what you need, but I would recommend trying to configure the Multi-Row tools yourself as well - this is one of the more powerful/dynamic tools since it allows you to work with data not just in the current record but in n number of rows before/after the current one! 

Hope that helps.

Cheers,

NJ

[ponraj · Accepted solution]

Attaching a sample workflow for your case. Hope this helps. 

[Shank]

Thank you so much for the solution.. :)

[Shank]

Thank You so much this really helped me to ease the work 5 Stars for you :)

[ponraj]

@Shank, would like to know the solution which helped you really:)

[Shank]

Actually, You'r logic perfectly worked for me...

[Shank]

Hi @NicoleJohnson

Thanks for the detail explanation and more insight on the multi-row tool.

I learned the multi-row tool usage now. thanks once again for such a wonderful explanation.

Regard,

Shank