Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Compare current week order snapshot to previous weeks

Shank
8 - Asteroid

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

 

MaterialMaterial TypeOrder NumberSupplierDelivery DateDelivery Quantitysnapshot DateWeek
1Raw1234ABC2018-07-02156/6/2018 12:2923
2Finished1235ABC2018-07-02116/6/2018 12:2923
3Finished1236ABC2018-07-02206/6/2018 12:2923
4Finished1237ABC2018-07-02136/6/2018 12:2923
1Raw1234ABC2018-07-02106/12/2018 12:2924
2Finished1235ABC2018-07-02116/12/2018 12:2924
3Finished1236ABC2018-07-02126/12/2018 12:2924
4Finished1237ABC2018-07-02136/12/2018 12:2924
5Finished1238ABC2018-07-02146/12/2018 12:2924
6Finished1239ABC2018-07-02156/12/2018 12:2924
3Finished1236ABC2018-07-02126/12/2018 12:2924
4Finished1237ABC2018-07-02136/17/2018 12:2925
5Finished1238ABC2018-07-02146/17/2018 12:2925
6Finished1239ABC2018-07-02156/17/2018 12:2925
7Finished1240ABC2018-07-02226/17/2018 12:2925
8Finished1241ABC

2018-07-02

246/17/2018 12:2925

 

 

Output

\

MaterialMaterial TypeOrder NumberSupplierDelivery DateDelivery Quantitysnapshot DateCurrent WeekOrder previous WeekSnapshot DatePrevious Week NoPrivious Week Delivery DatePrevious Week QuantityRemarks
4Finished1237ABC7/12/2018136/17/2018 12:292512376/12/2018 12:29242018-07-0210 
4Finished1237ABC7/12/2018136/17/2018 12:292512376/6/2018 12:29232018-07-0213 
 1Finished 1234ABC 2018-07-02 156/6/2018 12:292312346/6/2018 12:29232018-07-0215Deleted from Current Week
8Finished1241ABC2018-07-02246/17/2018 12:2925     New Order



7 REPLIES 7
NicoleJohnson
ACE Emeritus
ACE Emeritus

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]

 

Multi-Row Formula Tool.JPG

 

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 number of rows before/after the current one! 

 

Hope that helps.

 

Cheers,

NJ

 

 

ponraj
13 - Pulsar

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

 

workflow.PNGResults.PNG

Shank
8 - Asteroid
Thank you so much for the solution.. :)
Shank
8 - Asteroid

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

ponraj
13 - Pulsar

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

Shank
8 - Asteroid
Actually, You'r logic perfectly worked for me...
Shank
8 - Asteroid

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

Labels