Comparing 3 columns based on the Peek and Previous Rows

[nizamsha]

Hello Everyone ,

I am Comparing 3 Columns based on the Peek and Previous rows 

I have a Data Set like below 

Check:
Load * Inline [
Port_Code,Discharge_Code,Origin_Code,Destination_Code,EDA,Consol,Shipment,Ledger
NLBON,NLRTM,NLRTM,USTUZ,6/9/2021,C2101010394,S2102423880,1
NLRTM,BEANR,NLRTM,USTUZ,6/10/2021,C2101233964,S2102423880,1
BEANR,DEBRE,NLRTM,USTUZ,6/11/2021,C2100848297,S2102423880,1
DEBRV,PTSIE,NLRTM,USTUZ,6/12/2021,C2100841090,S2102423880,1
PTSIE,USLGB,NLRTM,USTUZ,6/13/2021,C2100841090,S2102423880,2

NLBON,NLRTM,NLRTM,CATOR,7/21/2021,C2101202340,S2103209067,1
NLRTM,BEANR,NLRTM,CATOR,7/23/2021,C2101589272,S2103209067,1
BEANR,CAMTR,NLRTM,CATOR,7/30/2021,C2101120141,S2103209067,1
CAYUL,CAYYZ,NLRTM,CATOR,8/19/2021,C2101802737,S2103209067,1

KRPUS,THBKK,KRPUS,THBKK,6/6/2021,C2101098022,S2102268124,1

PUSKR,BKKTH,KRPUS,THBKK,8/6/2021,C2101098025,S2102268125,1

SESOE,DEHAM,SESOE,MYPEN,1/18/2021,C2100009128,S2100017137,1
DEHAM,SGSIN,SESOE,MYPEN,1/31/2021,C2100009128,S2100017137,2
SGSIN,MYPEN,SESOE,MYPEN,3/10/2021,C2100009128,S2100017137,3

];

I am expecting the Output like the below .Output Columns are OutPutCheck1,OutputCheck2 andFinal Check.

Sorting should be based on the Shipment and EDA Date so the above data set i kept in the sorting order..

Scenario : OutPutCheck1

My first Port_Code Row should Match with the First row of Origin_Code then my Second Port_Code Row should Match with the First row of Discharge_Code  then then my 3rd  Port_Code Row should Match with the 2nd row of Discharge_Code like that it has to check till the Last Port Code  for the individual Shipment ..

Scenario : OutPutCheck2

My first Discharge_Code Row should Match with the 2nd row of Port_Code then my Second Dischrge_Code Row should Match with the 3rd row of Port_Code  then then my 3rd  Discharge_Code Row should Match with the 4th row of PortCode_Code then Last Discharge_Code  row should match with the Last Row of Destination Code based on individual shipment 

Scenario : FinalCheck

IF both the OutPutCheck1 and OutPutCheck2 is True then its a valid Port.

if either of the OutPutCheck1 and OutPutCheck2 is False  or either of the output is false then its a InvalidPort..

if the shipment Count is only one then OutPutCheck1 and OutPutCheck2 is True then its a Only one Transport

if the shipment Count is only one then OutPutCheck1 or OutPutCheck2 is False then its a Invalid Port

Check:
Load * Inline [
Port_Code,Discharge_Code,Origin_Code,Destination_Code,EDA,Consol,Shipment,Ledger,OutputCheck1,OutputCheck2,FinalCheck
NLBON,NLRTM,NLRTM,USTUZ,6/9/2021,C2101010394,S2102423880,1,F,T,Invlid Port
NLRTM,BEANR,NLRTM,USTUZ,6/10/2021,C2101233964,S2102423880,1,T,T,Valid Port
BEANR,DEBRE,NLRTM,USTUZ,6/11/2021,C2100848297,S2102423880,1,T,F,Invlid Port
DEBRV,PTSIE,NLRTM,USTUZ,6/12/2021,C2100841090,S2102423880,1,F,T,Invlid Port
PTSIE,USLGB,NLRTM,USTUZ,6/13/2021,C2100841090,S2102423880,2,T,F,Invlid Port

NLBON,NLRTM,NLRTM,CATOR,7/21/2021,C2101202340,S2103209067,1,F,T,Invlid Port
NLRTM,BEANR,NLRTM,CATOR,7/23/2021,C2101589272,S2103209067,1,T,T,vlid Port
BEANR,CAMTR,NLRTM,CATOR,7/30/2021,C2101120141,S2103209067,1,T,F,Invlid Port
CAYUL,CAYYZ,NLRTM,CATOR,8/19/2021,C2101802737,S2103209067,1,F,F,Invlid Port

KRPUS,THBKK,KRPUS,THBKK,6/6/2021,C2101098022,S2102268124,1,T,T,Only One Transprot

PUSKR,BKKTH,KRPUS,THBKK,8/6/2021,C2101098025,S2102268125,1,F,F,Invalid Port

SESOE,DEHAM,SESOE,MYPEN,1/18/2021,C2100009128,S2100017137,1,T,T,valid Port
DEHAM,SGSIN,SESOE,MYPEN,1/31/2021,C2100009128,S2100017137,2,T,T,valid Port
SGSIN,MYPEN,SESOE,MYPEN,3/10/2021,C2100009128,S2100017137,3,T,T,valid Port

];

Regards

NJ

[nizamsha · Accepted solution]

Hi Saran ,

Thanks for your time finally i 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