How to get unmatched records using LOAD statement?

shumailh — Tue, 30 Jun 2009 23:51:09 GMT

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

How to get unmatched records using LOAD statement?

Wed, 01 Jul 2009 13:32:42 GMT

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;

How to get unmatched records using LOAD statement?

shumailh — Wed, 01 Jul 2009 16:25:37 GMT

Thanks for the example Amit

Regards,
Shumail Hussain

topic How to get unmatched records using LOAD statement? in QlikView

How to get unmatched records using LOAD statement?

How to get unmatched records using LOAD statement?

How to get unmatched records using LOAD statement?