Comparing 2 columns but different Rows

[nizamsha]

Hello Everyone ,

I have a data set like below

i am expecting an output Column based on the Column above Destination,Port,Discharge Code ..

Here by Attaching the Excel with an Expected Output Column  Marked in Yellow with the Formula and also Red Color is the Data Set

Regards

Nizam

[nizamsha]

Hi Everyone ,

How can we solve the above solution ..

Regards

Nizam

[Vegar]

You need to make sure you have the data ordered in the opposite order, then you can compare each row you load with the previous loaded row using peek().

Take a look at the sample script below.

OrderedPTP:
LOAD 
Consol, 
     Shipment, 
     Ledger, 
     Origin_Code, 
     Destination_Code ,
     Port_Code, 
     Discharge_Code, 
     OutPut as [Desired output], 
     EDA, 
     Shipment_Count, 
     Row_Count,      
	 IF( Peek('Port_Code') = Discharge_Code , 1, 0) as [Calculated output]
Resident PTP 
Order By Row_Count desc;
drop table PTP;

[nizamsha]

Hi Vegar ,

Thanks for the Input but it was not working as expected ..

Kindly look at the attached excel where yellow is highlighted It has the Formula too .

FYI : From 1st Row of Output till the 4th Row of OutPut we have one formula but when comes to 5th row we have another Formula as per the attached Last excel file  ..

For better understanding i am sharing the original excel in that highlighted yellow column is the output we are expecting and also have formula in that ..

Thanks for your Time and support in advance

Regards

Nizam

[nizamsha]

Hi Vegar ,

I have attached  the excel with the Formula in this thread ..

But as a Scenario i have explained in another thread too here by giving the url for that thread  to give more Clarity : https://community.qlik.com/t5/New-to-Qlik-Sense/Comparing-3-columns-based-on-the-Peek-and-Previous-R... 

Regards

Nizam

[nizamsha]

Hi Vegar ,

How can i fix the issue ...

Regards

Nizam

[Vegar]

I am not really following exactly what you are trying to do. There is a lot going on in your attached Excel file.

Could it be that you want to break the comparison when you switch from one Shipment to another. If so then you could try to check if the Shipment has changed by an if statement like this.

=IF(peek('Shipment')= Shipment ,
    IF(Peek('Port_Code')=Discharge_Code,1,0),
    0) as [Calculated output]

[nizamsha]

Hello Vegar ,

Thanks for the Reply .. as per your reply yes we want to break the shipment one to another  based on EDA Dates ASC..

Lets Consider this Shipment S2102423880 it has 5 Rows 

but then  what we want to do is

1st Row of discharge Code has to match with the 2nd row of Port Code 

Then 2nd Row of discharge Code has to match with the 3rd row of Port Code 

Then 3rd Row of discharge Code has to match with the 4th row of Port Code 

Then 4th Row of discharge Code has to match with the 5th row of Port Code 

Then Finally 

5th Row of Discharge Code has to match will the 5 th row of Destination Code like that 

PFA

Regards

Nizam

[nizamsha · Accepted solution]

Hi Vegar ,

Thanks for your time Finally got the output ..

Data_Temp:
LOAD
RowNo() as Key,
//RowNo() as Key_temp,
Consol,
Shipment,
Port_Code,
Discharge_Code,
Origin_Code,
Destination_Code,
EDA,
Num(Date(EDA)) as EDA_Num,
Ledger
FROM [lib://FRP_Forwin/Port to port logic check_27 Aug 21.xlsx]
(ooxml, embedded labels, table is Sheet1);

Data:

Load *,
AutoNumber(RowNo(),Shipment)as RowNum
Resident Data_Temp
Order by Shipment,EDA_Num;

Drop Table Data_Temp;

Left Join(Data)
Load Shipment,
Count(Key) as RowCount
Resident Data
Group by Shipment;

DataFinal:
Load *,
If(RowNum=1 and RowCount=1,'1L',If(RowNum=1,'FL',If(RowNum=RowCount,'LL','ML'))) as LegType,
Peek(Discharge_Code) as Prev_Discharge_Code
Resident Data
Order by Key;

Left Join(DataFinal)
Load *,
Peek(pc) as Next_Port_Code;
Load Key,
Port_Code as pc
Resident Data
Order by Key desc;

Drop Table Data;

Drop Field pc;

Fact:
Load *,
If(LegType='1L',
If(Port_Code=Origin_Code and Discharge_Code=Destination_Code,'One Leg Transport','Invalid'),
If(LegType='FL',
If(Port_Code=Origin_Code and Discharge_Code=Next_Port_Code,'Valid','Invalid'),
If(LegType='LL',
If(Port_Code=Prev_Discharge_Code and Discharge_Code=Destination_Code,'Valid','Invalid'),
If(LegType='ML',
If(Port_Code=Prev_Discharge_Code and Discharge_Code=Next_Port_Code,'Valid','Invalid'),
'NA')))) as Check
Resident DataFinal;

Drop Table DataFinal;

Regards

Nizam

[Vegar]

That's great. Glad you where able to solve it. 

BR Vegar