Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
This works! Thanks