Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
philbishop
Contributor

tag as others

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

tag as others

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

philbishop
Contributor

tag as others

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

tag as others

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.

Community Browser