Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Thanks for the example Amit
Regards,
Shumail Hussain