Applymap with Dual

[StacyCui]

I wrote a script to define the order of the dimensions, but in the frontend, all these dimensions are displayed as "?". Is there a problem with the field types?

Category_mapping:
mapping load * inline [
DI China Branch,Value
VM Water & Wastewater ,9
VM Automotive,1
VM Food & Beverages,2
VM Pharma,4
VM Metal,5
VM Electronics,6
VM Batteries,7
VM Tire,8
VM Intralogistics,10
VM Smart Learning and Professional Education,11
HM Additive Manufacturing,12
];

load

dual( if(MixMatch("DI China Branch",
'VM Electronics',
'VM Water & Wastewater',
'VM Automotive',
'VM Metal',
'VM Food & Beverages',
'VM Pharma',
'VM Intralogistics',
'VM Batteries',
'HM Additive Manufacturing',
'VM Tire',
'VM Smart Learning and Professional Education' )>0,"DI China Branch"),ApplyMap('Category_mapping',"DI China Branch",'NULL')) as "DI China Branch"

resident XX table;

outer join

Load *
Inline [
Period
P1
P2
P3
P4
P5
P6
P7
P8
P9
P10
P11
P12
YTD
];

[Daniel_Pilla]

Hi @StacyCui ,

It looks like you want to have a dual() field value for each category if it exists, else just the string value. The below will achieve this. Note that I included a category that doesn't exist intentionally.

Category_mapping:
mapping load 
	*,
    RecNo() AS Value
inline [
    DI China Branch
    VM Water & Wastewater
    VM Automotive
    VM Food & Beverages
    VM Pharma
    VM Metal
    VM Electronics
    VM Batteries
    VM Tire
    VM Intralogistics
    VM Smart Learning and Professional Education
    HM Additive Manufacturing
];

temp:
load * inline [
    DI China Branch
    VM Electronics
    VM Water & Wastewater
    VM Automotive
    VM Metal
    VM Food & Beverages
    VM Pharma
    VM Intralogistics
    VM Batteries
    HM Additive Manufacturing
    VM Tire
    VM Smart Learning and Professional Education
    I don't exist
];
 
data:
noconcatenate load
Alt(dual("DI China Branch",ApplyMap('Category_mapping',"DI China Branch")),"DI China Branch") as "DI China Branch"
resident temp;

drop table temp;
outer join (data)

Load *
Inline [
    Period
    P1
    P2
    P3
    P4
    P5
    P6
    P7
    P8
    P9
    P10
    P11
    P12
    YTD
];

.

[marcus_sommer]

Just for sorting the branch it's not mandatory necessary to create a dual() or an extra sorting field else loading them in the wanted order would be enough, maybe like:

cartesian:
load "DI China Branch" inline [
DI China Branch,Value
VM Automotive,1
VM Food & Beverages,2
VM Pharma,4
VM Metal,5
VM Electronics,6
VM Batteries,7
VM Tire,8
VM Water & Wastewater ,9
VM Intralogistics,10
VM Smart Learning and Professional Education,11
HM Additive Manufacturing,12
];

outer join(cartesian)

Load *
Inline [
Period
P1
P2
P3
P4
P5
P6
P7
P8
P9
P10
P11
P12
YTD
];

and then enabling the load-order as sorting-rule within the objects.