Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
How about other items prefixes that not exist in CLASS CRITERIA should tag as OTHERS... How to do this?
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
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.