How to get unmatched records using LOAD statement?

[shumailh]

I have two load statement named as V2 and V3, How can i join both tables that i can get unmatched records from both?

V2:
LOAD CARD_NO,'P' as V2,LoadDate as LoadDate_V2,CARD_NO as CARD2, BASE_SEGMENT_NBR AS BASE_SEGMENT_NBR2
, MID(CARD_NO,1,17) as Mid_14_2, MID(CARD_NO,18,1) as Replacement2
RESIDENT FGBCHB
WHERE
round(CUR_BAL)<>0 AND
(
 (index('LF', BLOCK1) AND index('ACDFGILNOPQTUVWXZ ', BLOCK2) AND index('ACDFGILNOPQTUVWXZ ', BLOCK3)) OR
 (index('ACDFGILNOPQTUVWXZ ', BLOCK1) AND index('LF',BLOCK2) AND index('ACDFGILNOPQTUVWXZ ', BLOCK3)) OR
 (index('ACDFGILNOPQTUVWXZ ', BLOCK1) AND index('ACDFGILNOPQTUVWXZ ', BLOCK2) AND index('LF', BLOCK3))
);

V3:
LOAD CARD_NO,'P' as V3,LoadDate as LoadDate_V3,CARD_NO as CARD3, BASE_SEGMENT_NBR AS BASE_SEGMENT_NBR3
, MID(CARD_NO,1,17) as Mid_14_3, MID(CARD_NO,18,1) as Replacement3
RESIDENT FGBCHB
WHERE NOT Exists(CARD1, CARD_NO) and NOT Exists(CARD2, CARD_NO);

Below is the sql server query which i wanted to convert in Load statement in QlikView.

SELECT s2.BASE_SEGMENT_NBR, max(s2.[card_no]) as card_no, s2.Mid_14, max(s2.Replacement) as Replacement
FROM vwActiveCard_Step2 s2
left outer join vwActiveCard_Step3 s3 on s2.BASE_SEGMENT_NBR = s3.BASE_SEGMENT_NBR and s2.mid_14 = s3.mid_14
where s3.BASE_SEGMENT_NBR is null and s3.card_no is null group by s2.BASE_SEGMENT_NBR, s2.Mid_14

for more detail http://community.qlik.com/forums/p/17567/68869.aspx#68869

[#2 · Accepted solution]

An example solution for un-matching records:

-- First table
V1:
LOAD V1
FROM xyz

-- Second table
V2:
LOAD V2
FROM xyz

-- All records from both tables
All:
load V1 as V
resident V1;
concatenate (All)
load V2 as V
resident V2;

-- Common records
M:
load V as m
resident All
where exists(V1,V) and exists(V2,V);

-- Unmatching records
U:
load V as u
resident All
where not exists(m,V);

-- drop unwanted tables
drop tables All,M;

[shumailh]

Thanks for the example Amit

Regards,
Shumail Hussain