5 Replies Latest reply: Dec 7, 2017 7:23 AM by max payne RSS

    Showing Values on Condition in QlikView

    Abhishek Malik

      Hi All,

       

      I am trying to do something in Qlikview. It goes as this:

       

      Tab:

      LOAD * INLINE [

      code, supplier, docno, value

      BQ1234, v1, 1200034215, 1000

      BQ1234, v1, 1200041235, 500

      BQ1234, v1, 2210034215, 1000

      BQ1234, v2, 1200045612, 500

      BQ1234, v2, 2210045612, 500

      BQ1234, v3, 2210045211, 300

      ];

       

      The Docno starting from '12' are old ones and those starting from '22' are the new ones. Now what i want is that if a the last 5 digits of a docno is present in both new and old docno, then the value in front of old one should become zero and the new one should show as it is in the final result.

       

      For now what i am doing is:

      Table1:

      Load

      Right(docno,5) as docno1,

      value as value1

      Resident Tab

      where Left(docno,2)='22';

       

       

      Let vfieldcount=FieldValueCount('docno1');

       

       

      For i=0 to $(vfieldcount)-1

      Let vFieldvalue=peek('docno1',$(i),'Table1');

       

      Load

      code,

      supplier,

      docno as docnoold,

      //if(right(docno,5)=$(vFieldvalue),docno,docno) as docnoold,

      if(right(docno,5)=$(vFieldvalue),0,value) as valuenew

      Resident Tab

      where Left(docno,2)='12';

       

       

       

       

      NEXT i;

       

      Concatenate

      Load

      code,

      supplier,

      docno as docnoold,

      value as valuenew

      Resident Tab

      where Left(docno,2)<>'12';

       

       

      Drop table Table1;

      Drop table Tab;

      EXIT Script;

        • Re: Showing Values on Condition in QlikView
          Sunny Talwar

          Try this

           

          Table:

          LOAD *,

          Left(docno, 2) as Key1;

          LOAD * INLINE [

              code, supplier, docno, value

              BQ1234, v1, 1200034215, 1000

              BQ1234, v1, 1200041235, 500

              BQ1234, v1, 2210034215, 1000

              BQ1234, v2, 1200045612, 500

              BQ1234, v2, 2210045612, 500

              BQ1234, v3, 2210045211, 300

          ];

           

          FinalTable:

          LOAD *,

          Right(docno, 5) as Key2

          Resident Table

          Where Key1 = 22;

           

          Concatenate (FinalTable)

          LOAD *,

          Right(docno, 5) as Key2

          Resident Table

          Where Key1 = 12 and not Exists(Key2, Right(docno, 5));

           

          DROP Table Table;

          • Re: Showing Values on Condition in QlikView
            max payne

            Try below

             

            Tab:

            LOAD * INLINE [

            code, supplier, docno, value

            BQ1234, v1, 1200034215, 1000

             

             

            BQ1234, v1, 1200041235, 500

             

             

            BQ1234, v1, 2210034215, 1000

             

             

            BQ1234, v2, 1200045612, 500

             

             

            BQ1234, v2, 2210045612, 500

             

             

            BQ1234, v3, 2210045211, 300

             

             

            ];

             

             

            temp:

            Load docno as new_doc,

            right(docno,5) as testNewDoc

            Resident Tab

            where left(docno,2)='22';

             

             

            left join(Tab)

            temp1:

            Load docno as docno,

            right(docno,5) as testOldDoc ,

            '1' as flag

            Resident Tab

            where left(docno,2)='12'

            and Exists(testNewDoc,right(docno,5));

             

             

             

             

            NoConcatenate

            Final:

            Load *,if(flag=1,0,value) as new_value Resident Tab;

             

             

            Drop table Tab;

             

             

            Regards,