topic Re: Match() weird issue in QlikView

Match() weird issue

Wed, 29 Jul 2015 12:53:41 GMT

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.

Re: Match() weird issue

sunny_talwar — Wed, 29 Jul 2015 12:57:49 GMT

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?

Re: Match() weird issue

sgrice — Wed, 29 Jul 2015 12:58:13 GMT

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;

Re: Match() weird issue

Wed, 29 Jul 2015 13:08:17 GMT

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.

Re: Match() weird issue

tresB — Wed, 29 Jul 2015 13:08:54 GMT

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

sunny_talwar — Wed, 29 Jul 2015 13:09:54 GMT

Good one tresesco

Re: Match() weird issue

Wed, 29 Jul 2015 13:10:55 GMT

Steven I tried that, no change

Re: Match() weird issue

Wed, 29 Jul 2015 13:14:56 GMT

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.

Re: Match() weird issue

sunilkumarqv — Wed, 29 Jul 2015 13:15:15 GMT

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');

Re: Match() weird issue

Wed, 29 Jul 2015 13:20:53 GMT

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

Wanted Item: BYSMW00060001

Unwanted Item: BYSMW00600001

Re: Match() weird issue

sgrice — Wed, 29 Jul 2015 13:27:07 GMT

I think that the issue might be that you are using a resident table. and your new results are just appending to the old ones

OVERAL_SUMMARY_TAB_TEMP:

NoConcatenate

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');

DROP TABLE SUMMARY_OVERAL;

Re: Match() weird issue

Wed, 29 Jul 2015 13:39:49 GMT

I want the item number: BYSMW00060001 not BYSMW00600001 included in the table. Therefore I tried:

OVERAL_SUMMARY_TAB_TEMP:

NoConcatenate

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');

Still no change.

PLEASE NOTE I AM USING summary_overal in other charts and sheets and cannot drop it.

Re: Match() weird issue

Wed, 29 Jul 2015 13:42:00 GMT

Btw I am uploading the qvw and datasource so you can try to diagnosis with better accuracy and reload the result.

Re: Match() weird issue

Wed, 29 Jul 2015 13:42:21 GMT

Btw I am uploading the qvw and datasource so you can try to diagnosis with better accuracy and reload the result.

Re: Match() weird issue

sunny_talwar — Wed, 29 Jul 2015 13:42:28 GMT

and did you drop SUMMARY_OVERALL?

DROP Table SUMMARY_OVERALL;

Re: Match() weird issue

Wed, 29 Jul 2015 13:42:35 GMT

Btw I am uploading the qvw and datasource so you can try to diagnosis with better accuracy and reload the result.

Re: Match() weird issue

Wed, 29 Jul 2015 13:42:47 GMT

Btw I am uploading the qvw and datasource so you can try to diagnosis with better accuracy and reload the result.

Re: Match() weird issue

Wed, 29 Jul 2015 15:49:15 GMT

no that table is needed somewhere else. If it is necessary to drop it, my other tables' data become problematic. This table is SUMMARY_OVERALL just for these 4 Items.
If you think this is the issue, how can I maintain my other tables data?

Re: Match() weird issue

Wed, 29 Jul 2015 15:52:54 GMT

I dropped the SUMMARY_OVERALL for testing. still no change pal

Re: Match() weird issue

swuehl — Wed, 29 Jul 2015 16:18:23 GMT

Sorry, in which chart do you see that field ITEM_NO_IN_SUMMARY_TABLE = BYSMW00600001 still exists?

I am looking at your LAST MONTH COPY application, and there are four possible values for that field:

BACTMW0050001

BACTMW0150001

BYSMW00060001

BYSMW00150001

Seems pretty much what you filter in your WHERE clause

...'BACTMW0150001','BYSMW00150001','BACTMW0050001','BYSMW00060001')

So, could you point me to the exact occurence of the mysterious value?