Comparing Yesterday's Orders to Today's

[prees959]

Hi,

Each day we receive an new Orders Dataset :

Yesterdays:

Order ID	Shipped By	Status
1	02/02/2018	Processed
2	06/01/2018	In Progress
3	02/02/2018	In Progress
4	02/02/2018	In Progress
5	05/01/2018	In Progress
6	05/01/2018	Processed
7	05/01/2018	Processed
8	05/01/2018	Processed
9	28/12/2017	Cancelled
10	17/01/2018	Processed

Todays:

Order ID	Shipped By	Status
1	02/02/2018	Processed
2	06/01/2018	Processed
3	02/02/2018	In Progress
4	02/02/2018	Processed
5	05/01/2018	Processed
6	05/01/2018	Processed
7	05/01/2018	Processed
8	05/01/2018	In Progress
10	17/01/2018	Processed
11	22/01/2018	Processed

Required Outcome (Latest New Processed Orders)

Order ID	Shipped By	Status
2	06/01/2018	Processed
4	02/02/2018	Processed
5	05/01/2018	Processed
11	22/01/2018	Processed

I need to link the latest Processed Orders to a Details Database which takes hours to run as I am running it on 'Today's' dataset as a whole to ensure I have the most upto date 'Processed Order IDs'  It would be great if I could compare the two tables and end up with a 'Final' table where I only need to run the 'Details'  section for 'Today's' new Processed records - and append the new Order Details to to yesterdays order details file.

I've tried using a 'where not exists' on Order ID but it is is bringing all records through as opposed to just the new records. Also, as the Status can change back and forth due to amendments (eg see Order 😎 - is causing me some confusion.....

Also, is it possible to create a second table for those records that were 'Processed' in yesterdays file - but now have a different Status today eg Order 8 or not it the new dataset at all?

If anyone can share any advice - that would be great.

Many thanks,

Phil

[Anonymous · Accepted solution]

Hi Phil,

You'll need to create a Key which uses the OrderID and the Status in order to get updated status records.

Code below, example attached;

Yesterday:

Load *, OrderID &'-'& Status as Key;

LOAD OrderID,

     [Shipped By],

     Status

FROM

data.xlsx

(ooxml, embedded labels, table is Yesterday);

NoConcatenate

Today:

LOAD OrderID,

     [Shipped By],

     Status

FROM

data.xlsx

(ooxml, embedded labels, table is Today)

Where Not Exists(Key, OrderID &'-'& Status)

and Status = 'Processed';

Drop Table Yesterday;

[Anil_Babu_Samineni]

May be try this? I created Flag like Todays and Yesterday

If(Only({<Flag = {'Todays'}>} Status) = Only({<Flag = {'Yesterdays'}>} Status), Status)

[prees959]

Thanks!! Worked Perfectly.

If I wanted to to it the other way around and find out which records exist in 'yesterdays' and not in 'Todays' - is there a similar solution?

Many thanks again.

Phil

[Anonymous]

No problem,

If you wanted it to work the other way simply switch the load around so Today's data is loaded 1st and Yesterdays 2nd;

Today:

Load *, OrderID &'-'& Status as Key;

LOAD OrderID,

     [Shipped By],

     Status

FROM

data.xlsx

(ooxml, embedded labels, table is Today);

NoConcatenate

Yesterday:

LOAD OrderID,

     [Shipped By],

     Status

FROM

data.xlsx

(ooxml, embedded labels, table is Yesterday)

Where Not Exists(Key, OrderID &'-'& Status)

and Status = 'Processed';

Drop Table Today;