Exluding records from a primary table

[shumailh]

I am trying to parse my data on scripting environment of Qlikview but i am not sure about the validity of the resultant of the below code. plus i feel the code i am doing is not optimized or there will be a more efficient way to write below.

Can any one please help and let me know either the table V3 would provide me the valid data by using the inner join clause OR Is there any other way to exclude records from the primary table i.e. $(CurrentFileName)?

$(V1):
Load CARD_NO, 'P' as V1, LoadDate as LoadDate_V1 resident $(CurrentFileName)
WHERE (
(MATCH(BLOCK1,'U','V','W','D','G','L','R','F','X') AND Round(Num#(CUR_BAL)) <= 0) OR
(MATCH(BLOCK2,'U','V','W','D','G','L','R','F','X') AND Round(Num#(CUR_BAL)) <= 0) OR
(MATCH(BLOCK3,'U','V','W','D','G','L','R','F','X') AND Round(Num#(CUR_BAL)) <= 0)
) OR (BLOCK1 = 'R' OR BLOCK2 = 'R' OR BLOCK3 = 'R');

//*********************************************************************************************************************************

$(V2):
Load CARD_NO, 'P' as V2, LoadDate as LoadDate_V2 resident $(CurrentFileName)
WHERE
(
 (
 BLOCK1='L' Or BLOCK1='F'
 ) AND
 (
 Match(BLOCK2 ,'A' ,'C' ,'D' ,'F' ,'G' ,'I' ,'L' ,'N' ,'O' ,'P' ,'Q' ,'T' ,'U' ,'V' ,'W' ,'X' ,'Z', '') OR
 (trim(BLOCK2) = '')
 ) AND
 (
 (trim(BLOCK3) ='') OR
 Match(BLOCK3 ,'A' ,'C' ,'D' ,'F' ,'G' ,'I' ,'L' ,'N' ,'O' ,'P' ,'Q' ,'T' ,'U' ,'V' ,'W' ,'X' ,'Z', '' )
 ) AND
 (
 NUM#(CUR_BAL) <> 0
 )
) OR
(
 (
 BLOCK2='L' Or BLOCK2='F'
 ) AND
 (
 Match(BLOCK1 ,'A' ,'C' ,'D' ,'F' ,'G' ,'I' ,'L' ,'N' ,'O' ,'P' ,'Q' ,'T' ,'U' ,'V' ,'W' ,'X' ,'Z', '') OR
 (trim(BLOCK1) = '')
 ) AND
 (
 (trim(BLOCK3) ='') OR
 Match(BLOCK3 ,'A' ,'C' ,'D' ,'F' ,'G' ,'I' ,'L' ,'N' ,'O' ,'P' ,'Q' ,'T' ,'U' ,'V' ,'W' ,'X' ,'Z', '' )
 ) AND
 (
 NUM#(CUR_BAL) <> 0
 )
) OR
(
 (
 BLOCK3='L' Or BLOCK3='F'
 ) AND
 (
 Match(BLOCK1 ,'A' ,'C' ,'D' ,'F' ,'G' ,'I' ,'L' ,'N' ,'O' ,'P' ,'Q' ,'T' ,'U' ,'V' ,'W' ,'X' ,'Z', '') OR
 (trim(BLOCK1) = '')
 ) AND
 (
 (trim(BLOCK2) ='') OR
 Match(BLOCK2 ,'A' ,'C' ,'D' ,'F' ,'G' ,'I' ,'L' ,'N' ,'O' ,'P' ,'Q' ,'T' ,'U' ,'V' ,'W' ,'X' ,'Z', '' )
 ) AND
 (
 NUM#(CUR_BAL) <> 0
 )
)
;




$(V3):
Load CARD_NO, 'P' as V3, LoadDate as LoadDate_V3 resident $(CurrentFileName);

inner join
Load CARD_NO resident V1
where NOT exists(CARD_NO);

inner join
Load CARD_NO resident V2
where NOT exists(CARD_NO);

[johnw · Accepted solution]

With some minor assumptions about your data - all BLOCK fields are a single character, there is only one row per CARD_NO - I get this:

V3:
LOAD CARD_NO,'P' as V3,LoadDate as LoadDate_V3
RESIDENT $(CurrentFileName)
WHERE NOT ( match('R',BLOCK1,BLOCK2,BLOCK3)
 OR ( round(num#(CUR_BAL))<=0
 AND ( index(BLOCK1,'UVWDGLFX')
 OR index(BLOCK2,'UVWDGLFX')
 OR index(BLOCK3,'UVWDGLFX'))))
 AND NOT ( num#(CUR_BAL)<>0
 AND ( ( index(BLOCK1,'LF')
 AND index(BLOCK2,'ACDFGILNOPQTUVWXZ ')
 AND index(BLOCK3,'ACDFGILNOPQTUVWXZ '))
 OR ( index(BLOCK1,'ACDFGILNOPQTUVWXZ ')
 AND index(BLOCK2,'LF')
 AND index(BLOCK3,'ACDFGILNOPQTUVWXZ '))
 OR ( index(BLOCK1,'ACDFGILNOPQTUVWXZ ')
 AND index(BLOCK2,'ACDFGILNOPQTUVWXZ ')
 AND index(BLOCK3,'LF'))));

Edit: Oh, you probably meant to keep the V1 and V2 tables. In that case, something more like this:

V1:
LOAD CARD_NO,'P' as V1,LoadDate as LoadDate_V1,CARD_NO as CARD
RESIDENT $(CurrentFileName)
WHERE match('R',BLOCK1,BLOCK2,BLOCK3)
 OR ( round(num#(CUR_BAL))<=0
 AND ( index(BLOCK1,'UVWDGLFX')
 OR index(BLOCK2,'UVWDGLFX')
 OR index(BLOCK3,'UVWDGLFX')))
;
V2:
LOAD CARD_NO,'P' as V2,LoadDate as LoadDate_V2,CARD_NO as CARD
RESIDENT $(CurrentFileName)
WHERE num#(CUR_BAL)<>0
 AND ( ( index(BLOCK1,'LF')
 AND index(BLOCK2,'ACDFGILNOPQTUVWXZ ')
 AND index(BLOCK3,'ACDFGILNOPQTUVWXZ '))
 OR ( index(BLOCK1,'ACDFGILNOPQTUVWXZ ')
 AND index(BLOCK2,'LF')
 AND index(BLOCK3,'ACDFGILNOPQTUVWXZ '))
 OR ( index(BLOCK1,'ACDFGILNOPQTUVWXZ ')
 AND index(BLOCK2,'ACDFGILNOPQTUVWXZ ')
 AND index(BLOCK3,'LF')))
;
V3:
LOAD CARD_NO,'P' as V3,LoadDate as LoadDate_V3
RESIDENT $(CurrentFileName)
WHERE NOT EXITS(CARD,CARD_NO)
;
DROP FIELD CARD
;

[shumailh]

Hi John,

Many thanks for your help, I tried the second module of code you posted, you are great. thanks

Regards,
Shumail Hussain

[shumailh]

Hi Jhon!

Below are the sequence of (SQL Server) Views which i wanted to replicate on QlikView. These views allows us to massage the data to extract the final report. You already updated me the 3 views and i have still 2 views remaining to go to the final step.

SQL Server Views (5 Steps) and Final Query

CREATE VIEW vwActiveCard_Step1
as
SELECT [Card_No] FROM cdbs
WHERE ( (BLOCKCODE1 In ('u','v','w','d','g','l','r','f','x') AND CUR_BAL= 0) OR
 (BLOCKCODE2 In ('u','v','w','d','g','l','r','f','x') AND CUR_BAL= 0) OR (BLOCKCODE3 In ('u','v','w','d','g','l','r','f','x') AND CUR_BAL= 0) )
OR( BLOCKCODE1 = 'R' OR BLOCKCODE2 = 'R' OR BLOCKCODE3 = 'R' )
GO
CREATE VIEW vwActiveCard_Step2
as
--14
SELECT BASE_SEGMENT_NBR,[card_no]
, substring([card_no],1,17) as Mid_14, substring([card_no],18,1) as Replacement
FROM cdbs
WHERE
((BLOCKCODE1='L' Or BLOCKCODE1='F') AND (BLOCKCODE2 in ('A' ,'C' ,'D' ,'F' ,'G' ,'I' ,'L' ,'N' ,'O' ,'P' ,'Q' ,'T' ,'U' ,'V' ,'W' ,'X' ,'Z', '' ) OR BLOCKCODE2 IS NULL) AND (BLOCKCODE3 IS NULL OR BLOCKCODE3 in ('A' ,'C' ,'D' ,'F' ,'G' ,'I' ,'L' ,'N' ,'O' ,'P' ,'Q' ,'T' ,'U' ,'V' ,'W' ,'X' ,'Z', '' )) AND (cur_bal<>0)) OR
((BLOCKCODE2='L' Or BLOCKCODE2='F') AND (BLOCKCODE1 in ('A' ,'C' ,'D' ,'F' ,'G' ,'I' ,'L' ,'N' ,'O' ,'P' ,'Q' ,'T' ,'U' ,'V' ,'W' ,'X' ,'Z' , '') OR BLOCKCODE1 IS NULL) AND (BLOCKCODE3 IS NULL OR BLOCKCODE3 in ('A' ,'C' ,'D' ,'F' ,'G' ,'I' ,'L' ,'N' ,'O' ,'P' ,'Q' ,'T' ,'U' ,'V' ,'W' ,'X' ,'Z', '' )) AND (cur_bal<>0)) OR
((BLOCKCODE3='L' Or BLOCKCODE3='F') AND (BLOCKCODE1 in ('A' ,'C' ,'D' ,'F' ,'G' ,'I' ,'L' ,'N' ,'O' ,'P' ,'Q' ,'T' ,'U' ,'V' ,'W' ,'X' ,'Z', '' ) OR BLOCKCODE1 IS NULL) AND (BLOCKCODE2 IS NULL OR BLOCKCODE2 in ('A' ,'C' ,'D' ,'F' ,'G' ,'I' ,'L' ,'N' ,'O' ,'P' ,'Q','T' ,'U' ,'V' ,'W' ,'X' ,'Z', '' )) AND (cur_bal<>0))
GO
CREATE VIEW vwActiveCard_Step3 as
SELECT --*
BASE_SEGMENT_NBR, F.Card_No, substring(card_no,1,17) as Mid_14,substring(card_no,18,1) as Replacement
 FROM cdbs F
WHERE F.card_no NOT IN (SELECT S1.card_no FROM vwActiveCard_Step1 S1) AND
F.card_no NOT IN (SELECT S2.card_no FROM vwActiveCard_Step2 S2)
GO
CREATE VIEW vwActiveCard_Step4 as
SELECT s2.BASE_SEGMENT_NBR, max(s2.[card_no]) as card_no, s2.Mid_14, max(s2.Replacement) as Replacement --, s2.CUR_BAL
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--, s2.CUR_BAL
GO
CREATE VIEW vwActiveCard_Step5 as
SELECT S4.BASE_SEGMENT_NBR, S2.CARD_NO--, S2.[BlockCode1], S2.[BlockCode2], S2.[BlockCode3], S2.[CUR_BAL], S2.Replacement
FROM vwActiveCard_Step4 S4
LEFT OUTER JOIN vwActiveCard_Step2 S2 ON (S4.BASE_SEGMENT_NBR = S2.BASE_SEGMENT_NBR AND S4.Mid_14 = S2.Mid_14 AND S4.Replacement = S2.Replacement)
GO

//Final Step
SELECT c.*, substring(c.card_no,1,14) as Mid_14, substring(c.card_no,15,1) as Replacement, d.Bucket
Into [Active]
FROM cdbs c
inner JOIN Bucket d ON c.DELQ_DAYS = d.DelqDay
left outer JOIN vwactivecard_step1 b ON b.card_no = c.card_no
left outer JOIN vwactivecard_step2 a ON a.card_no = c.card_no
WHERE b.card_no Is Null AND a.card_no Is Null

below is the code which i have replicated for the QlikView, Kindly check if there's any mistake

V1:
LOAD CARD_NO,'P' as V1,LoadDate as LoadDate_V1,CARD_NO as CARD1, BASE_SEGMENT_NBR AS BASE_SEGMENT_NBR1
, MID(CARD_NO,1,17) as Mid_14_1, MID(CARD_NO,18,1) as Replacement1
RESIDENT $(CurrentFileName)
WHERE
match('R',BLOCK1,BLOCK2,BLOCK3) OR
(
round(CUR_BAL)=0 AND
(
index(BLOCK1,'UVWDGLRFX') OR
index(BLOCK2,'UVWDGLRFX') OR
index(BLOCK3,'UVWDGLRFX')
)
)
;
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 $(CurrentFileName)
WHERE
round(CUR_BAL)<>0 AND
(
(index(BLOCK1,'LF') AND index(BLOCK2,'ACDFGILNOPQTUVWXZ ') AND index(BLOCK3,'ACDFGILNOPQTUVWXZ ')) OR
(index(BLOCK1,'ACDFGILNOPQTUVWXZ ') AND index(BLOCK2,'LF') AND index(BLOCK3,'ACDFGILNOPQTUVWXZ ')) OR
(index(BLOCK1,'ACDFGILNOPQTUVWXZ ') AND index(BLOCK2,'ACDFGILNOPQTUVWXZ ') AND index(BLOCK3,'LF'))
);
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 $(CurrentFileName)
WHERE NOT Exists(CARD1, CARD_NO) and NOT Exists(CARD2, CARD_NO);
V4:
LOAD Max(CARD_NO) as CARD_NO,'P' as V4, Max(LoadDate_V2) as LoadDate_V4, Max(CARD_NO) as CARD4, BASE_SEGMENT_NBR2 as BASE_SEGMENT_NBR4
, Mid_14_2 AS Mid_14_4, Max(Replacement2) AS Replacement4
RESIDENT V2
WHERE
not Exists(BASE_SEGMENT_NBR3, BASE_SEGMENT_NBR2) and
not Exists(Mid_14_3, Mid_14_2)
group by BASE_SEGMENT_NBR2, Mid_14_2;
//and IsNull(BASE_SEGMENT_NBR3)= -1 and IsNull(CARD3)= -1;

V5:
LOAD CARD_NO,'P' as V5,LoadDate_V4 as LoadDate_V5,CARD_NO as CARD5, BASE_SEGMENT_NBR4 as BASE_SEGMENT_NBR5
, Mid_14_4 AS Mid_14_5, Replacement4 AS Replacement5
RESIDENT V4
WHERE Exists(BASE_SEGMENT_NBR2, BASE_SEGMENT_NBR4) and Exists(Mid_14_2, Mid_14_4) and Exists(Replacement2, Replacement4);
store V1 into $(Outputpath)V1.qvd ;
store V2 into $(Outputpath)V2.qvd ;
store V3 into $(Outputpath)V3.qvd ;
store V4 into $(Outputpath)V3.qvd ;
store V5 into $(Outputpath)V3.qvd ;

The Problem in the above code is that all the Load statement are not giving me the exact resultant as i am getting from SQL Server views...

[shumailh]

Block fields contain values like 'A', 'C', 'D' etc... so for that field i have replace the code as

index('ACDFGILNOPQTUVWXZ ', BLOCK2)

which has been previuosly written as

index(BLOCK2,'ACDFGILNOPQTUVWXZ ')

and giving me no records. so now after replacement of code view 1, 2, and 3 has been reconciled. but I am still stuck with view 4 and 5.