Multi box value not filtering correctly

[triciagdaly]

I am loading a value 'Retrieved' from a spreadsheet for specific items in the spreadsheet.  The 'retrieved' values for items that are not listed are correctly pulling into qlikview as null.  However, when trying to filter with  'No', the results are not correct.  The expression for this data filter is :

=if(IsNull(Reviewed),'No','Yes')

Filtering for values = 'Yes' are correct.  What am I missing?  I have attached a test version of the qvw and the spreadsheet for reference.

[#2]

Hi Tricia, You data model needs to be modified. I can see several synthetic keys in you data model.please try to remove all synthetic keys you will not get accurate reports until you fix your data model. try to concatenate data  into your purchase_orders table. or ling other tables using a unique ID. Try to maintain start topology in your data structure. Alsoby looking at your excel sheet  for all your records your Received value='X' therefore you will not have any non review data.

[triciagdaly]

With the left join to the purchase order table, would it not default to null for all items that aren't listed in the spreadsheet?

[rwunderlich · Accepted solution]

Hi Tricia,

I'm not a fan of synthetic keys, but I don't think they are the source of the problem you asked about. The problem is that you can't select Nulls (missing values) in this way. Your expression in the Multibox identifies the null Reviewed fields with "N", but you can't select those fields. I'd recommend you assign a value to the missing values.

Take a look at this post for a technique to populate the missing values

Filling Default Values Using Mapping | Qlikview Cookbook

-Rob

http://masterssummit.com

http://robwunderlich.com

[#5]

Can you try

If(Len(Trim(Reviewed))=0,'No','Yes') as Flag

in the script.

Thanks

[triciagdaly]

Rob,

The below corrected the problem… may be less overhead than reloading entire table. I was previously doing a similar thing with another field, so made sense to add a few more lines. The thing for me to remember is to convert the nulls to a value before filtering Thank you for your help!

po_comments:

left join(purchase_orders)

LOAD

DISTINCT text(PO) & '|' & text(Position)&'|'&text(Company) as pkey,

Item as ITEM_NUMBER,

Note,

as Expedite_PO,

if(LEFT(UPPER(Reviewed),1)='X','Yes','No') as Reviewed_PPV

FROM

(ooxml, embedded labels, table is )

where not isnull(PO);

ExpeditePO:

left join(purchase_orders)

LOAD pkey,

if(isnull(Expedite_PO),EXPEDITE,Expedite_PO) as Exp,

if(isnull(Reviewed_PPV),'No',Reviewed_PPV) as Reviewed

resident purchase_orders;

drop fields Expedite_PO,EXPEDITE, Reviewed_PPV;

rename field Exp to Expedite;

[rwunderlich]

Hi Trica,

Glad you found a solution. However, if your find solution taking too long, I would encourage you to revisit the mapping solution. You are actually reloading the entire table, plus added overhead of if() and join(). Reloading as LOAD * Resident would typically be faster.

-Rob