topic Left Join in QlikView

Left Join

Tue, 29 Mar 2011 04:53:35 GMT

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

Left Join

Tue, 29 Mar 2011 06:20:39 GMT

Left Join([Cebu Detail])

LOAD * Resident [Cebu Header]

Where not Exists([Cebu Header_BatchNo],[Cebu Header_TrxNo]);

[Temp Cebu]:

LOAD Distinct

[%Cebu_Key],

if(IsNull([Cebu Header_Key])=-1,'NULL',[Cebu Header_Key]) as [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] as [Invoice Number],

[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

Resident [Cebu Detail]

Where Trim([Cebu Header_BatchNo])=Trim([Cebu Detail_BatchNo]) AND Trim([Cebu Header_TrxNo])=Trim([Cebu Detail_TrxNo])

OR [Cebu Detail_Key]<>[Cebu Header_Key];

DROP Fields [Cebu Detail_Key],[Cebu Header_Key];

DROP Tables [Cebu Detail],[Cebu Header];

Left Join

marcohadiyanto — Tue, 29 Mar 2011 06:22:23 GMT

hi,

my analysis from your problem that you want to join header with detail, and only to show data from detail only?

if you wanna do that, you put right join, not left join.

regards,