consolidate duplicate rows into one single row

[1600eads]

The data currently has [Process] and [OperationsTimestamp] of when an order is ordered ad shipped. Here is a snippet of the current data followed by what I want my output to be.

desired output:

Much thanks everyone. 

[therealdees]

Hi,

I can imagine a couple ways of doing this, but I'm not sure about how your data is structured. Anyway, the code below did the job:

MainData:
LOAD * Inline [CustomerID|Process|OperationsTimestamp|Difference_in_Timestamp
122|ORDERED|January 2, 2024|5 days
122|SHIPPED|January 7, 2024|5 days
] (delimiter is '|');

MapOrderDate:
Mapping LOAD
CustomerID
,OperationsTimestamp
Resident MainData
Where Process = 'ORDERED';

MapDayDiff:
Mapping LOAD
CustomerID
,Difference_in_Timestamp
Resident MainData
Where Process = 'SHIPPED';

FinalTable:
NoConcatenate LOAD
CustomerID
,Process
,ApplyMap('MapOrderDate', CustomerID) as PendingTimestamp
,OperationsTimestamp as ShippedTimestamp
,ApplyMap('MapDayDiff', CustomerID) as Difference_in_Timestamp
Resident MainData
Where Process = 'SHIPPED';

Drop Table MainData;

If your date field is actually a date field not a text, you won't need the mapping for the days difference, you could just substract the dates.

[rubenmarin · Accepted solution]

Hi, I suppose that a customer could have more than one Order, so you'll need OrderID or similar to get the state of  each Order.

Fist have a temporrtfy table with orders data like tmpOrders, from this table you can crete the final table.

To get the date you can make different mapping tables or make some joins

mapLastState:
Mapping LOAD CustomerID, Process
Resident tmpOrders
Order By CustomerID, OperationsTimestamp desc;

 Start with al the different OrderID

FinalTable:
NoConcatenate LOAD Distinct CustomerID From tmpOrders;

Add the data of each state (if there is more than one record for each Order-State it will be better to do a mpping table) to fill the columns

Left Join (FinalTable)
LOAD CustomerID,
  OperationsTimestamp as PendingTimestamp,
  Applymap('mapLastState',CustomerID) as Process
Resident tmpOrders
Where Process='ORDERED';
  
Left Join (FinalTable)
LOAD CustomerID,
  OperationsTimestamp as ShippedTimestamp,
Differnce_in_timestamp
Resident tmpOrders
Where Process='SHIPPED';

DROP Table tmpOrders;

 Probably you'll need to use OrderID instead of CustomerID

[Ahidhar]

try this

tab:
load customerid,process,
date#(operationstimestamp,'MMMM D,YYYY') as operationstimestamp,
difference_in_timestamp Inline
[
customerid|process|operationstimestamp|difference_in_timestamp
122|ordered|January 2,2024|5 Days
122|shipped|January 7,2024|5 Days
123|ordered|January 3,2024|6 Days
123|shipped|January 9,2024|6 Days
]
(delimiter is '|');

tab1:
load customerid,'shipped' as process,
operationstimestamp as pendingtimestamp,
date(operationstimestamp+left(difference_in_timestamp,1),'MMMM D,YYYY') as shippedtimestamp,
difference_in_timestamp
resident tab where process='ordered';drop table tab;