Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
rsapaull
Contributor III
Contributor III

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

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 NumberSTATUSMax (Order_Date)Sum([Order value])
2044HOrder Received01/09/2015               345.88
2044HIn Progress28/08/2015            2,906.03
2234COrder Sent02/01/2015            5,658.22
2234COrder Received05/09/2015            1,163.55
2234CIn Progress02/09/2015               872.66
2964HOrder Received01/09/2015            1,359.90
2964HIn Progress31/08/2015           89,929.61
3215HIn Progress08/09/2015               550.35
3215HOrder Received09/09/2015            1,033.63
3220HOrder Received06/09/2015            1,506.94
3220HIn Progress08/09/2015           11,028.21
3317HOrder Received10/09/2015            3,554.70
3317HIn Progress03/09/2015            3,010.69
3369TOrder Received20/08/2015               474.71
3369TIn Progress29/08/2015         289,368.98
3430TOrder Sent02/01/2015           26,536.64
3430TCancelled01/01/2015                   0.00
3431TOrder Received01/09/2015            1,996.23
3431TIn Progress02/08/2015            2,972.64
3445HOrder Received01/09/2015           24,243.34
3445HIn Progress15/08/2015           44,058.61
3470HOrder Sent02/06/2015            1,178.10
3470HIn Progress25/08/2015            5,900.66
3609HOrder Received04/09/2015            2,396.92
3609HIn Progress05/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.

1 Solution

Accepted Solutions
Anonymous
Not applicable

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.

View solution in original post

4 Replies
gautik92
Specialist III
Specialist III

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

rsapaull
Contributor III
Contributor III
Author

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

Not applicable

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
Not applicable

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.