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
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;
