Good Day!
I have a Detail and a Header Table. the problem is that a certain data exists in Detail Table but not in the Header Table.
what I want to do is to join those two tables and show the data that exists in the detail table but not in the header data..
here's my script:
Header:
LOAD Trim(BatchNo & '-' & TrxNo) as [%Cebu_Key],
Trim(BatchNo & '-' & TrxNo) as [Cebu Header_Key],
BatchNo as [Cebu Header_BatchNo],
TrxNo as [Cebu Header_TrxNo],
Trucker as [Cebu Header_Trucker],
DeclaredValue as [Cebu Header_DeclaredValue],
Customer as [Cebu Header_Customer],
Destination as [Cebu Header_Destination],
Case as [Cebu Header_Case],
Pack as [Cebu Header_Pack],
Box as [Cebu Header_Box],
Piece as [Cebu Header_Piece],
TotalCBM as [Cebu Header_TotalCBM],
RatePerCBM as [Cebu Header_RatePerCBM],
OTD as [Cebu Header_OTD],
FreightCost as [Cebu Header_FreightCost],
ValChargeSurfaceFee as [Cebu Header_ValChargeSurfaceFee],
TotalAmt as [Cebu Header_TotalAmt],
withWayBill as [Cebu Header_withWayBill],
withVat as [Cebu Header_withVat]
FROM Source
Detail:
LOAD BatchNo & '-' & TrxNo as [%Cebu_Key],
BatchNo & '-' & TrxNo as [Cebu Detail_Key],
BatchNo as [Cebu Detail_BatchNo],
TrxNo as [Cebu Detail_TrxNo],
GPInvc as [Cebu Detail_GPInvc],
FACInvc as [Cebu Detail_FACInvc],
Amount as [Cebu Detail_Amount],
Case as [Cebu Detail_Case],
Pack as [Cebu Detail_Pack],
Box as [Cebu Detail_Box],
Piece as [Cebu Detail_Piece],
CBM as [Cebu Detail_CBM]
FROM Source
//Join
Left Join([Cebu Detail])
LOAD * Resident [Cebu Header];
[Temp Cebu]:
LOAD [%Cebu_Key],
[Cebu Header_Key],
//[Cebu Header_BatchNo],
//[Cebu Header_TrxNo],
[Cebu Header_Trucker],
[Cebu Header_DeclaredValue],
[Cebu Header_Customer],
[Cebu Header_Destination],
[Cebu Header_Case],
[Cebu Header_Pack],
[Cebu Header_Box],
[Cebu Header_Piece],
[Cebu Header_TotalCBM],
[Cebu Header_RatePerCBM],
[Cebu Header_OTD],
[Cebu Header_FreightCost],
[Cebu Header_ValChargeSurfaceFee],
[Cebu Header_TotalAmt],
[Cebu Header_withWayBill],
[Cebu Header_withVat],
[Cebu Detail_BatchNo],
[Cebu Detail_TrxNo],
[Cebu Detail_GPInvc],
[Cebu Detail_FACInvc],
[Cebu Detail_Amount],
[Cebu Detail_Case],
[Cebu Detail_Pack],
[Cebu Detail_Box],
[Cebu Detail_Piece],
[Cebu Detail_CBM],
[Cebu Detail_Key],
if([Cebu Header_Trucker]='ALJEM',1,0) as ALJEM_Flag,
if([Cebu Header_Trucker]='EXPLORER',1,0) as EXPLORER_Flag,
if([Cebu Header_Trucker]='F2',1,0) as F2_Flag,
if([Cebu Header_OTD]=0 or IsNull([Cebu Header_OTD]),1,0) as OTDFlag,
if(IsNull([Cebu Header_Key])=1,'is NULL',[Cebu Header_Key]) as [Cebu Header_KeyFlag]
Resident [Cebu Detail]
Where Trim([Cebu Detail_BatchNo])=Trim([Cebu Header_BatchNo]) AND Trim([Cebu Detail_TrxNo])=Trim([Cebu Header_TrxNo])
;
but qlikview show only those data that exists in both table..
