topic Re: Multi box value not filtering correctly in QlikView

Multi box value not filtering correctly

triciagdaly — Wed, 14 Jan 2015 22:43:25 GMT

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.

Re: Multi box value not filtering correctly

Wed, 14 Jan 2015 23:04:42 GMT

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.

Re: Multi box value not filtering correctly

triciagdaly — Wed, 14 Jan 2015 23:07:16 GMT

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?

Re: Multi box value not filtering correctly

rwunderlich — Thu, 15 Jan 2015 00:17:26 GMT

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

Re: Multi box value not filtering correctly

Thu, 15 Jan 2015 00:20:52 GMT

Can you try

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

in the script.

Thanks

Re: Multi box value not filtering correctly

triciagdaly — Thu, 15 Jan 2015 15:30:47 GMT

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;

Re: Multi box value not filtering correctly

rwunderlich — Thu, 15 Jan 2015 16:56:35 GMT

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