Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

tag as others

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

CLASSQTY
LAPTOP10
LAPTOP-OTHERS

5

MONITOR-OTHERS1

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.

4 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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

Anonymous
Not applicable
Author

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

Not applicable
Author

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.