Grouping in Load Script

[jlampard40]

Hi guys - I need to group some fields into common groups, so that I can then use a list box to select in my dashboard.

The dimension is called:  AD_Room

There are many outputs in this dimension but I need to group them by 'External' and 'Internal'.

The 'External' rooms are fields:  CTMOB; MSPI; XSPI; CSPI; CTMOB2

The 'Internal' rooms are fields:  RMR2; SMR1; SMR2; SMR3; SMR4; SMR5

All other rooms can be grouped as 'Other Rooms'.

How can I do this in the load script??  Thanks.

[Taoufiq_Zarra]

@jlampard40  you can use Mapping table like this :

MapField:
mapping load * inline [
FieldtoMap,Mapby
CTMOB,External
MSPI,External
XSPI,External
CSPI,External
CTMOB2,External
RMR2,Internal
SMR1,Internal
SMR2,Internal
SMR3,Internal
SMR4,Internal
SMR5,Internal
];

YOURTABLE:

load 
Applymap('MapField',FieldtoMap,'Other Rooms') as New_Field,
...

[jlampard40]

Where would I put this in the load script Taoufiq?  This is my load script (shortened a little):

Directory;
LOAD
AD_Referring_Location as Referring_Location,
AD_Data as Activity_or_Demand,
AD_Booked,
AD_Room as Room,
AD_Reported_By as Reported_By,
AD_Demand_Group as Demand_Group,
AD_CY as Calendar_Year_R

FROM
[..\Python\DASH_AD.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

[Taoufiq_Zarra]

@jlampard40  yes like :

MapField:
mapping load * inline [
AD_Room,Mapby
CTMOB,External
MSPI,External
XSPI,External
CSPI,External
CTMOB2,External
RMR2,Internal
SMR1,Internal
SMR2,Internal
SMR3,Internal
SMR4,Internal
SMR5,Internal
];

Table:
Directory;

LOAD
AD_Referring_Location as Referring_Location,
AD_Data as Activity_or_Demand,
AD_Booked,
AD_Room as Room,
Applymap('MapField',AD_Room,'Other Rooms') as New_AD_Room,
AD_Reported_By as Reported_By,
AD_Demand_Group as Demand_Group,
AD_CY as Calendar_Year_R

FROM
[..\Python\DASH_AD.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

[jlampard40]

Hi Taoufiq - that works really well.  What if I want to add a condition to this grouping, so that some scanners are external, some internal, some are external inpatient, some are external outpatient.

The dimensions are AD_Room and AD_PatientType

So, as before, the 'External' rooms are fields:  CTMOB; MSPI; XSPI; CSPI; CTMOB2

The 'Internal' rooms are fields:  RMR2; SMR1; SMR2; SMR3; SMR4; SMR5

But I also want to have external inpatient which are:  CTMOB; MSPI; XSPI; CSPI; CTMOB2 and patient type 'A' (from dimension AD_PatientType).

External Outpatient will be CTMOB; MSPI; XSPI; CSPI; CTMOB2 and patient type 'B' (from dimension AD_PatientType).

All other rooms can be grouped as 'Other Rooms' again.

Many thanks again!

[Taoufiq_Zarra]

@jlampard40  sorry for the late, we don't receive notification if I'm not tagged.

can you share a sample data ? and the expected ?