Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
Highlighted
Not applicable

Match() weird issue

Hi all

I've this very straight forward expression:

OVERAL_SUMMARY_TAB_TEMP:

LOAD

ITEM_NO_IN_SUMMARY_TABLE AS ITEM_NO_IN_SUMMARY_TAB,

CUST_NO_IN_SUMMARY_TABLE AS CUST_NO_IN_SUMMARY_TAB,

CUST_TYPE_IN_SUMMARY AS CUST_TYPE_IN_SUMMARY_TAB,

SUM_BY_ITEM_NUMBER AS SUM_BY_ITEM_NUMBER_FOR_SUMMARY_TAB,

MOQ AS MOQ_FOR_SUMMARY_TAB

Resident SUMMARY_OVERAL

WHERE Match(ITEM_NO_IN_SUMMARY_TABLE,'BACTMW0150001','BYSMW00150001','BACTMW0050001','BYSMW00060001');

but in my table I still have items with ITEM_NO_IN_SUMMARY_TABLE = BYSMW00600001

Download the qvw with datasources from here: Dropbox - Beverage Penetration and 4K_20150622.zip

Please pay attention that the included item number is not in match statement. Please help.

1 Solution

Accepted Solutions
MVP
MVP

Re: Match() weird issue

Still missing your issue.

I am looking at "4K Tracking and Beverage - LAST MONTH - Copy.qvw"

You are using

OVERAL_SUMMARY_TAB_TEMP:

LOAD

ITEM_NO_IN_SUMMARY_TABLE AS ITEM_NO_IN_SUMMARY_TAB,

CUST_NO_IN_SUMMARY_TABLE AS CUST_NO_IN_SUMMARY_TAB,

CUST_TYPE_IN_SUMMARY AS CUST_TYPE_IN_SUMMARY_TAB,

SUM_BY_ITEM_NUMBER AS SUM_BY_ITEM_NUMBER_FOR_SUMMARY_TAB,

MOQ AS MOQ_FOR_SUMMARY_TAB

Resident SUMMARY_OVERAL

WHERE Match(Trim(ITEM_NO_IN_SUMMARY_TABLE),'BACTMW0150001','BYSMW00150001','BACTMW0050001', 'BYSMW00060001');

Thus renaming ITEM_NO_IN_SUMMARY_TABLE to ITEM_NO_IN_SUMMARY_TAB.

If I look at the field values of ITEM_NO_IN_SUMMARY_TAB:

BACTMW0050001

BACTMW0150001

BYSMW00060001

BYSMW00150001

I don't see the unwanted item. Do you?

The original table SUMMARY_OVERAL and field ITEM_NO_IN_SUMMARY_TABLE shows:

BACTMW0050001

BACTMW0150001

BYSMW00060001

BYSMW00150001

BYSMW00600001

But that's not the table you've filtered!

I don't see your issue please point me to the occurence of your filtered field showing a value not contained in the match() of the WHERE clause.

View solution in original post

21 Replies

Re: Match() weird issue

Is it possible that you are loading the same field ITEM_NO_IN_SUMMARY_TABLE at another place may be which is not restricted by the same Match() in where clause?

Partner
Partner

Re: Match() weird issue

Match returns 0 if no match

Change

WHERE Match(ITEM_NO_IN_SUMMARY_TABLE,'BACTMW0150001','BYSMW00150001','BACTMW0050001','BYSMW00060001');

WHERE Match(ITEM_NO_IN_SUMMARY_TABLE,'BACTMW0150001','BYSMW00150001','BACTMW0050001','BYSMW00060001')>0;

Not applicable

Re: Match() weird issue

Yes. This Summary table is the only table which filters by these specific item numbers. But I have carefully checked only this single table with pivot table, chart and export of the table and the wrong item number shows up in this table.

MVP
MVP

Re: Match() weird issue

Or, sometimes it could be because of unwanted spaces in the data. Try like:

Where Match(Trim(ITEM_NO_IN_SUMMARY_TABLE),'BACTMW0150001', ....

Re: Match() weird issue

Good one tresesco

Not applicable

Re: Match() weird issue

Steven I tried that, no change

Not applicable

Re: Match() weird issue

tresesco‌ Tried it. No change yet. The table remains untouched after the LOAD statement I can assure you. It also filters out other Item Numbers!!!! just this one (BYSMW00600001) remains in.

sunilkumarqv
Valued Contributor II

Re: Match() weird issue

I noticed you included in where condition

ITEM_NO_IN_SUMMARY_TABLE = BYSMW00600001

Try this

OVERAL_SUMMARY_TAB_TEMP:

LOAD

ITEM_NO_IN_SUMMARY_TABLE AS ITEM_NO_IN_SUMMARY_TAB,

CUST_NO_IN_SUMMARY_TABLE AS CUST_NO_IN_SUMMARY_TAB,

CUST_TYPE_IN_SUMMARY AS CUST_TYPE_IN_SUMMARY_TAB,

SUM_BY_ITEM_NUMBER AS SUM_BY_ITEM_NUMBER_FOR_SUMMARY_TAB,

MOQ AS MOQ_FOR_SUMMARY_TAB

Resident SUMMARY_OVERAL

WHERE Match(ITEM_NO_IN_SUMMARY_TABLE,'BACTMW0150001','BYSMW00150001','BACTMW0050001');

Not applicable

Re: Match() weird issue

sunilkumarqv‌ good idea. After doing the modification It filters out both wanted and unwanted item.

Wanted Item: BYSMW00060001

Unwanted Item: BYSMW00600001