6 Replies Latest reply: Sep 9, 2014 9:19 AM by pavan Kumar RSS

    How To match data in two columns and display third coulmn

      I have to get a csv file into Qlikview. Base on two columns values I need to get the third column data.

       

      Col1  Col2  Col3

      1        2      100

      2         3     200

      1        2      500

      2         4      600

      2          4      500

       

      Only I need to get only red color comuns data. that is(  col1=1 and col2=2) and (Col1=2 and col2= 4) and exclude remaining.

       


      Thanks

      Pavan

        • Re: How To match data in two columns and display third coulmn
          jagan mohan rao appala

          Hi,

           

          Try like this

           

          LOAD

          *

          FROM Data.csv

          WHERE  Col1=1 and Col2=2) OR (Col1=2 and Col2= 4);

           

          Hope this helps you.

           

          Regards,

          Jagan.

          • Re: How To match data in two columns and display third coulmn
            xia ZHU

            hi,

             

            you can try create a new key in your first table

            AutoNumberHash128(col1&'|'&col1) as Newkey

             

            and create your second table like this

             

            load *

            from table

            where col1&'|'&col1='1|2'.

            • Re: How To match data in two columns and display third coulmn
              Pradip Sen

              Try like

              T1:

              LOAD * Inline [

              Col1,  Col2,  Col3
              1, 2,    100
              2, 3,    200
              1,      2,    500
              2,      4,    600
              2,      4,    500
              ]
              ;

              T2:
              NoConcatenate
              LOAD
                  
              Col1,
                  
              Col2,
                   Col3

              Resident T1 Where Col2 = (Col1*2);

               

              • Re: How To match data in two columns and display third coulmn

                I didnt get how u calculate Col3 data,I attached a sample file,hope from this file you get an idea..

                • Re: How To match data in two columns and display third coulmn
                  Srikanth P

                  Hi Pavan, the best approach is create the temp table with all the possible values you need and use the Where Exists function to load only possible values in the Temp Table like below:

                   

                  TEMP:

                  LOAD * INLINE [

                  KEY

                  1-2

                  2-4

                  ];

                   

                  TABLE:

                  LOAD Col1, Col2, Col3 from File.csv

                  Where Exists(KEY, Col1&'-'&Col2);

                   

                  Drop Table TEMP;

                    • Re: How To match data in two columns and display third coulmn

                      Hi Dathu

                       

                      the script is not able to recognise the field vaues in the Where Exists funtion. Please let me know the mistake in the following code.

                       


                      TEMP:
                      Load * Inline
                      [
                      Key
                      01-01
                      01-02
                      02-01
                      02-02
                      01-03
                      01-04
                      01-05
                      01-07
                      01-08
                      02-03
                      02-04
                      02-05
                      02-07
                      02-08
                      01-30
                      01-31
                      01-32
                      01-33
                      01-50
                      01-65
                      02-30
                      02-31
                      02-32
                      02-33
                      02-65
                        ];          

                      [OGIS Premium]:
                      Load [@1:3]  as [Rep-Off],
                           [@4:6] as [Cur-Cd],
                           [@7:8] as Org,
                           [@9:10] as [Mgr-Cd],
                           [@11:12] as [Maj-Lin],
                           [@13:14 ] as [Min-Lin],
                           [@15:21] as  [Iss-Comp],
                           [@22:23] as   [Prod-Src],
                           [@24:25] as  [Tran-Type],
                           [@26:27] as [Acct-Ln],
                           [@28:30] as [Prod-Off],
                           [@31:40] as [Polcy-No],
                           [@41:46] as [Cert-No],
                           [@47:56] as  [Claim-No],
                           [@57:63] as [Acct-Prd],
                           [@64:72] as [Plcy-Inc-Dt] ,
                           [@73:81] as [Prem-Eff-Dt],
                           [@82:90] as [Loc-Edit-Dt],
                           [@91:99] as [Accid-Dt],
                           [@100:108] as [Prem-Exp-Dt],
                           [@109:117] as [Claim-Cre-Dt],
                           [@118:118] as [Correct-Cd],
                           [@119:128] as [Plcy-Id],
                           [@129:129] as [Rsrve-Tp],
                           [@130:138] as [Req-No],
                           [@139:140] as [Meth-Of-Pay],
                           [@141:149] as [Ind-Acct-No],
                           [@150:154] as [Catas-No],
                           [@155:155]  as [Ho-Rev-Cd],
                           [@156:161] as [Prod-No],
                           [@162:163] as  [RI-Type],
                           [@164:170]  as [RI-Comp],
                           [@171:173] as [Cls-Prl],
                           [@174:189] as [Vess-Name],
                           [@190:197]  as Blank1,
                           [@198:201] as  [Cause-Of-Loss],
                           [@202:204] as [Inj-Dam],
                           [@205:207] as  [Anat-Prop],
                           [@208:210] as [Req-Rev-No],
                           [@211:214]  as Blank2,
                           [@215:220] as [RI-Stat-Id],
                           [@221:227] as Blank,
                           [@228:230] as [Cntry-Cd] ,
                           [@231:247] as [Grp-Curr-Amt],
                           [@248:252] as Blank3,
                           [@253:259] as [Acqu-Rte-Grp],
                           [@260:266]  as [Agy-Comm-Rte-Grp],
                           [@267:273] as [Fac-Rte-Grp],
                           [@274:274] as [Pay-Tp],
                           [@275:276] as [Loc-Br-Anal],
                           [@277:281]  as [Lob],
                           [@282:284]  as [Loc-Prd-Src],
                           [@285:285] as Blank4,
                           [@286:291] as [Ins-Stat-Cd1],
                           [@292:297] as [Ins-Stat-Cd2],
                           [@298:n] as Blank5
                          
                      FROM [D:\Project LAC Reconciliation UI\Data\OGIS\LEBANON-NOAL30.txt]
                      (fix, codepage is 1252, no eof)
                      Where Exists(Key, ActLine & '-' & TransType);
                      DROP Table TEMP;