How can I set a condition in my pivot table or script so that multiple (text) values become one

[rsapaull]

I have an issue where I get multiple status's come up on an order in my pivot table because the same client has multiple orders in progress.  What I want to do, is set some conditions so that if there are multiple statuses, they all get changed to match one.  Below is an example of my output:

Client Number	STATUS	Max (Order_Date)	Sum([Order value])
2044H	Order Received	01/09/2015	345.88
2044H	In Progress	28/08/2015	2,906.03
2234C	Order Sent	02/01/2015	5,658.22
2234C	Order Received	05/09/2015	1,163.55
2234C	In Progress	02/09/2015	872.66
2964H	Order Received	01/09/2015	1,359.90
2964H	In Progress	31/08/2015	89,929.61
3215H	In Progress	08/09/2015	550.35
3215H	Order Received	09/09/2015	1,033.63
3220H	Order Received	06/09/2015	1,506.94
3220H	In Progress	08/09/2015	11,028.21
3317H	Order Received	10/09/2015	3,554.70
3317H	In Progress	03/09/2015	3,010.69
3369T	Order Received	20/08/2015	474.71
3369T	In Progress	29/08/2015	289,368.98
3430T	Order Sent	02/01/2015	26,536.64
3430T	Cancelled	01/01/2015	0.00
3431T	Order Received	01/09/2015	1,996.23
3431T	In Progress	02/08/2015	2,972.64
3445H	Order Received	01/09/2015	24,243.34
3445H	In Progress	15/08/2015	44,058.61
3470H	Order Sent	02/06/2015	1,178.10
3470H	In Progress	25/08/2015	5,900.66
3609H	Order Received	04/09/2015	2,396.92
3609H	In Progress	05/08/2015	20,071.27

So in the above I want to set conditions as follows:

If 1 of the statuses is 'In Progress' then change all of them to say 'In Progress'

If there is a mix of statuses between Order Sent and Cancelled, change to order sent

I would rather this be done in the script if possible, but have no idea where to begin.

Thanks in advance.

[gautik92]

if(Count(Status)>1,'1',Status)

[rsapaull]

I think you've taken my subject line a bit too literally

[#4]

Try the following script,

Hope it will help you.

test:

LOAD *,  ClientNumber & '_' & Order_Date as Key INLINE [

    ClientNumber, STATUS, Order_Date, Ordervalue

    2044H, Order Received, 01/09/2015,                345.88

    2044H, In Progress, 28/08/2015,2,906.03

    2234C, Order Sent, 02/01/2015,             5,658.22

    2234C, Order Received, 05/09/2015,             1,163.55

    2234C, In Progress, 02/09/2015,                872.66

    2964H, Order Received, 01/09/2015,             1,359.90

    2964H, In Progress, 31/08/2015,            89,929.61

    3215H, In Progress, 08/09/2015,                550.35

    3215H, Order Received, 09/09/2015,             1,033.63

    3220H, Order Received, 06/09/2015,             1,506.94

    3220H, In Progress, 08/09/2015,            11,028.21

    3317H, Order Received, 10/09/2015,             3,554.70

    3317H, In Progress, 03/09/2015,             3,010.69

    3369T, Order Received, 20/08/2015,                474.71

    3369T, In Progress, 29/08/2015,          289,368.98

    3430T, Order Sent, 02/01/2015,            26,536.64

    3430T, Cancelled, 01/01/2015,                    0.00

    3431T, Order Received, 01/09/2015,             1,996.23

    3431T, In Progress, 02/08/2015,             2,972.64

    3445H, Order Received, 01/09/2015,            24,243.34

    3445H, In Progress, 15/08/2015,            44,058.61

    3470H, Order Sent, 02/06/2015,             1,178.10

    3470H, In Progress, 25/08/2015,             5,900.66

    3609H, Order Received, 04/09/2015,             2,396.92

    3609H, In Progress, 05/08/2015,            20,071.27

];

test1:

load

newc,

newc & '_' & SepKey as SepKey;

LOAD

ClientNumber as newc,

Date(Max(Date#(Order_Date, 'dd/MM/yyyy')), 'dd/MM/yyyy') as SepKey

Resident test

Group by ClientNumber;

inner join (test1)

LOAD Key as SepKey,

STATUS as NewStatus

Resident test;

Left join(test)

Load distinct newc as ClientNumber,

NewStatus as New Resident test1;

DROP Table test1;

DROP Field Key;

EXIT Script;

[Anonymous · Accepted solution]

Hi Paul this is how I would approach this.

Step 1 load your table as and call it OrdersTemp.

Step 2 mapping load those tickets with a status that is 'In Progress' as follows

mapinprogress: mapping load distinct  [Client Number],STATUS resident OrdersTemp where STATUS = 'In Progress';

mapordersent: mapping load distinct  [Client Number],STATUS resident OrdersTemp where STATUS = 'Order Sent';

Step 3 apply the maps to your data to work out the correct status you are looking for as follows:-

This must be a part of the load statement for the

Orders:

LOAD

applymap('mapinprogress', [Client Number],applymap('mapordersent',[Client Number],STATUS)) as ReportStatus,

*

resident OrdersTemp;

drop table OrdersTemp

The nested mapping is really powerful and will first check if the order is one with a status which is in progress and label it as such, it will then test and see if it includes a Order Sent status and label it as such.

Hope this helps.