topic tag as others in QlikView

tag as others

Mon, 21 May 2012 10:25:35 GMT

Hi, I have big problem...

I have these datasource...

ITEMS

ITEM CODE CLASS

LAP001 LAPTOP

LAP002 LAPTOP

DAILY SALES

ITEM CODE QTY

LAP002 2

LAP001 8

LAP999 5

MON888 1

So here's my problem, I should have report like this

CLASS	QTY
LAPTOP	10
LAPTOP-OTHERS	5
MONITOR-OTHERS	1

So, my question is, how can I tagged that ITEM CODE that is not exist in ITEM MAINTENANCE will be tagged as OTHERS... and tagging OTHERS depends on the first 3 characters... so i have another file below for the CLASS CRITERIA

CLASS CRITERIA

CLASS PREFIXES

LAPTOP LAP

MON MONITOR

Please help. Thanks.

tag as others

Anonymous — Mon, 21 May 2012 23:42:18 GMT

Try this:
Load the ITEMS table and add a second field for [ITEM CODE]
[ITEM CODE] as [ITEM CODE_ITEM]
Load the DAILY SALES as you are
Then CONCATENATE additional rows on the ITEMS table from the DAILY SALES table based on whether ITEM CODE already exists in [ITEM CODE_ITEM]. You can use the CLASS CRITERIA table as a mapping table to set the correct value for CLASS.

CONCATENATE (ITEMS)
LOAD DISTINCT
[ITEM CODE],
applymap('Class_Map',left([ITEM CODE],3) & '-OTHERS' as CLASS
Resident
DAILY_SALES
Where
not exists([ITEM CODE_ITEM],[ITEM CODE]);

-Phil

tag as others

Tue, 22 May 2012 01:30:11 GMT

How about other items prefixes that not exist in CLASS CRITERIA should tag as OTHERS... How to do this?

tag as others

Anonymous — Tue, 22 May 2012 13:05:10 GMT

The simplest way to accomplish this would be to just use the third parameter of the applymap().
Something like this (Note that I accidentally left out a right parenthesis from my applymap() code above):
trim(applymap('Class_Map',left([ITEM CODE],3),null()) & ' OTHERS') as CLASS

I added the trim() to remove the first space in case NULL is returned for the applymap().
-Phil

tag as others

Wed, 23 May 2012 02:38:26 GMT

Hi guys,

Thanks for the answers. I already did it, I have found out the wrong position of the mapping load, i put it after the applymap script instead of putting it before the applymap script. That's why it never works... Thanks.