So here's my problem, I should have report like this
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
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]);
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
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.