2 Replies Latest reply: Jun 19, 2017 3:46 PM by Karen Birbeck RSS

    I need a button that shows rows with a blank cell

    Karen Birbeck

      My objective is to have a button that lists purchase orders (POs) that do not align to a BLI.  These purchase order numbers are identified in the list of transactions -  the transactions table.  The BLI table also has purchase order numbers.  In a perfect world, all POs listed in the transaction table would also be in the BLI table. But not all alignments of BLI and PO are identified before the transaction arrives.  This really gets to the whole purpose of this QVW - to find transactions that aren't aligned to the budget (BLIs).

       

        • In the file there are two buttons:

       

          • The NoPO button lists TRANSACTIONS that don’t have a PO number

       

          • The NoBLI button (NOT WORKING) is intended to list PURHASE ORDERS with not BLI alignment = they are not in the Budget list of BLI.

       

      Please see attached.  I am trying to use the NoBLI_Flag, as I did the NoPO_Flag.  If a PO is not aligned to a BLI I want it to be shown when the blue button is clicked.

       

      With NoPO, the purple button and table, I was looking within a table for a blank value (no purchase order).  With NoBLI, I am trying to compare 2 tables - Budget/BLI and Transactions.  My attempt, the blue button and table, was to join the 2 tables and use the NoPO script. That did not work.  I get "0" for items that have a BLI, but nothing for those that don't.  Help?

       

       

       

        • Re: I need a button that shows rows with a blank cell
          Aar Kay

          try this

          Trxn:

          LOAD [Obj Acct],

               [Subsi- diary],

               [Account Description],

               [G/L Date],

               Month,

               Amount,

               [Purchase Order],

               If(Len(Trim([Purchase Order])) = 0, 1, 0) as NoPO_Flag

          FROM

          [U:\_Qlikview\Scripts\sample_trxn.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

           

          JOIN

           

           

          LOAD BLI,

               [Expense Type],

               [Current Mgr],

               BLI_Description,

               BLI_Vendor,

               Budget_Amt,

               [Purchase Order],

               PO_Amt

           

          FROM

          [U:\_Qlikview\Scripts\sample_bli.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          NoConcatenate

          FinalTrxn:

          Load *,

               If(Len(Trim(BLI)) = 0, 1, 0) as NoBLI_Flag

          Resident

               Trxn;

          Drop Table

               Trxn;