Combine 2 lines into 1 line for new table

[pascaldijkshoor]

Hello Everyone,

I want to know if it's possible in my situation to combine 2 lines into 1 line. I will explain the situation below:

For our warehouse, a line of data consists the following information:

Pallet Nr - Date - Transaction Type (Inbound/Outbound)

So every Pallet Nr has 1 or 2 lines. It has only an inbound line if it's still in the warehouse, and an in and outbound line if it has already left the warehouse. I have created 2 extra fields, inbound and outbound date. I used the following script to create those fields:

NoConcatenate

CirculationTime:

Load

PallerNr,

    if (TransactionType=50,"Date",Previous ("Date")) as Inbound_Date,

    if (TransactionType=51,"Date") as Outbound_Date,

    if (TransactionType=51,"Date") - if (TransactionType=50,"Date",Previous ("Date"))  as CirculationTime

Resident Table1

Order By PallerNr, Date, TransactionType;   

Drop Table Table1;

This result in the following results (example):

Pallet Nr	Inbound_Date	Outbound_Date
1	1-6-2018	-
1	1-6-2018	7-6-2018
2	6-6-2018	-
2	6-6-2018	13-6-2018
3	19-6-2018	-
4	22-6-2018	-
5	24-6-2018	-
5	24-6-2018	29-6-2018

So I have 2 lines for every Pallet Nr that left the warehouse (see pallet nr 1,2,5)  and 1 line for every Pallet Nr that is still in the warehouse (see pallet nr 3,4). Is it possible to create a table with 1 line per Pallet Nr? This would look something like this:

Pallet Nr	Inbound_Date	Outbound_Date
1	1-6-2018	7-6-2018
2	6-6-2018	13-6-2018
3	19-6-2018	No Outbound'
4	22-6-2018	No Outbound'
5	24-6-2018	29-6-2018

Thanks in advance

[ogautier62 · Accepted solution]

Hi

you could try like this :

warehouse:

load No,max(In) as indate,if(not(isnull(max(Out))),max(Out),'No outbound') as outdate, if(not(isnull(max(Out))),(max(Out)-max(In))) as rotation resident 'your table with two lines' group by No;

regards

[pascaldijkshoor]

This works! Thanks