Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pascaldijkshoor
Creator
Creator

Combine 2 lines into 1 line for new table

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 NrInbound_DateOutbound_Date
11-6-2018-
11-6-20187-6-2018
26-6-2018-
26-6-201813-6-2018
319-6-2018-
422-6-2018-
524-6-2018-
524-6-201829-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 NrInbound_DateOutbound_Date
11-6-20187-6-2018
26-6-201813-6-2018
319-6-2018No Outbound'
422-6-2018No Outbound'
524-6-201829-6-2018

Thanks in advance

1 Solution

Accepted Solutions
ogautier62
Specialist II
Specialist II

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

View solution in original post

2 Replies
ogautier62
Specialist II
Specialist II

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
Creator
Creator
Author

This works! Thanks