4 Replies Latest reply: Jun 28, 2009 6:55 PM by Shumail Hussain RSS

    Exluding records from a primary table

    Shumail Hussain

      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);


        • Exluding records from a primary table
          John Witherspoon

          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
          ;


            • Exluding records from a primary table
              Shumail Hussain

              Hi John,

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

              Regards,
              Shumail Hussain

              • Exluding records from a primary table
                Shumail Hussain

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

                  • Exluding records from a primary table
                    Shumail Hussain

                    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.