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