Solved: Comparing 3 columns based on the Peek and Previous... - Qlik Community

nizamsha · ‎2021-08-26

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 · ‎2021-09-06

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

View solution in original post

nizamsha · ‎2021-08-26

Hello Everyone ,

Is that my Requirement is Clear or Should i make it short to make it even clear..

Kindly let me know and help me to fix the problem

Regards

Nizam

Saravanan_Desingh · ‎2021-08-26

Your 'Only One Transport' is not clear. Because Row#2 also should be this. But you have not marked it.

Try this,

_Check:
LOAD RowNo() As RowID,*,If(IsNull(Peek(Discharge_Code)),Port_Code=Origin_Code,Port_Code=Peek(Discharge_Code)) As OutPutCheck1;
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
];

Check_:
NoConcatenate
LOAD *, If(OutPutCheck1 And OutPutCheck2,'Valid Port', 'Invalid Port') As FinalCheck;
LOAD *, If(IsNull(Peek(Port_Code)),Port_Code=Destination_Code,Peek(Port_Code)=Discharge_Code) As OutPutCheck2
Resident _Check
Order By RowID Desc;

Drop Table _Check;

Saravanan_Desingh · ‎2021-08-26

Output:

nizamsha · ‎2021-08-26

Hi Saran ,

Thanks for your Response and Time ..

As you said Only One Transport is not Clear i am Rewriting it again for understanding

if the Shipment Count is one like below

Scenario1:

Port_Code,Discharge_Code,Origin_Code,Destination_Code,EDA,Consol,Shipment,Ledger
KRPUS,THBKK,KRPUS,THBKK,6/6/2021,C2101098022,S2102268124,1

Here in this scenario Port_Code=Origin_Code and Discharge_Code=Destination_Code so we have to say this as only one transport now we are showing this invalid port its wrong

But in the below scenario it also has shipment Count one but !!!

Scenario2:

Port_Code,Discharge_Code,Origin_Code,Destination_Code,EDA,Consol,Shipment,Ledger
PUSKR,BKKTH,KRPUS,THBKK,8/6/2021,C2101098025,S2102268125,1

Here in this scenario Port_Code<>Origin_Code and Discharge_Code<>Destination_Code so we have to say this as invalid Port ..Hope this is clear

I also Found for this shipment S2100017137 its showing invalid Port for the First and 3 Ledger but it should be valid Port because 1st Row of Port_Code=1st row of Origin_Code and also 3 rd Row of Discharge_Code=3 rd Row of Destination_Code so it should be valid port ..

I thought below information might be helpful too

Shipment is the Lowest Level

Possibilites

One Shipment might have one consol

.2.One shipment has one consol but multiple Ledger .

3.One shipment has multiple Consol and multiple Leder

Regards

NJ

Saravanan_Desingh · ‎2021-08-26

I tried with whatever, I understand. Please run with you data and let me know the outcome. Thanks.

_Check:
LOAD RowNo() As RowID,*,Port_Code=Origin_Code As _1P1O, Discharge_Code=Destination_Code As _1Di1De,
	If(Peek(Consol)&Peek(Shipment)=Consol&Shipment,Port_Code=Peek(Discharge_Code)) As _2P1Di;
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
];

Left Join(_Check)
LOAD Shipment, Consol, Min(Ledger) As MiL, Max(Ledger) As MaL
Resident _Check
Group By Shipment, Consol
;

Left Join(_Check)
LOAD RowID, If(Ledger=MiL, If(_1P1O, If(_1Di1De, 'Only One Port', 'Valid Port'), 'Invalid Port'),If(_2P1Di, 'Valid Port', 'Invalid Port')) As Result
Resident _Check
;

Saravanan_Desingh · ‎2021-08-26

Output:

nizamsha · ‎2021-08-27

Hi Saran ,

Thanks for the Support and your Time but still i did int get the expected Result ..

Eg for the Particular Shipment S2102423880 Last Row of the Discharge_Code has to be equal to the Last row of Destination_Code ..if its equal then its a valid port USLGB=USLGB else invalid port but as per our data it s been like this USLGB=USTUZ so its a invalid port but as per your logic its showing valid port

Regards

NJ

nizamsha · ‎2021-08-27

Hi Saran ,

Thanks for the Time for looking in to it ..

Here by Attaching the Excel with the Formulas ..From Column i to Column O its a derived column with the Formula ,Expecting the Output in Qlik

Data Set is in the Columns from Column A to Column H ..

Regards

NJ

nizamsha · ‎2021-08-30

Hi Everyone ,

Kindly Help me to solve the above Logic ..

Regards

Nizam

Comparing 3 columns based on the Peek and Previous Rows