Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

I need a button that shows rows with a blank cell

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?

 

 

1 Solution

Accepted Solutions
aarkay29
Valued Contributor

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

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

(ooxml, embedded labels, table is Sheet1);

JOIN

LOAD BLI,

     [Expense Type],

     [Current Mgr],

     BLI_Description,

     BLI_Vendor,

     Budget_Amt,

     [Purchase Order],

     PO_Amt

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

FinalTrxn:

Load *,

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

Resident

     Trxn;

Drop Table

     Trxn;

2 Replies
aarkay29
Valued Contributor

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

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

(ooxml, embedded labels, table is Sheet1);

JOIN

LOAD BLI,

     [Expense Type],

     [Current Mgr],

     BLI_Description,

     BLI_Vendor,

     Budget_Amt,

     [Purchase Order],

     PO_Amt

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

FinalTrxn:

Load *,

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

Resident

     Trxn;

Drop Table

     Trxn;

Highlighted
Not applicable

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

Trying it now.  It seems to break my statistics box...