Combine 2 lines into 1 line for new table

pascaldijkshoor — Tue, 24 Jul 2018 14:00:15 GMT

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

Re: Combine 2 lines into 1 line for new table

ogautier62 — Tue, 24 Jul 2018 14:50:08 GMT

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

Re: Combine 2 lines into 1 line for new table

pascaldijkshoor — Wed, 25 Jul 2018 07:32:59 GMT

This works! Thanks

topic Re: Combine 2 lines into 1 line for new table in App Development

Combine 2 lines into 1 line for new table

Re: Combine 2 lines into 1 line for new table

Re: Combine 2 lines into 1 line for new table