Problem with mapping load

[#1]

Hi

I created a sample dashboard showing the problem I'm having.

I want to be able to map the Locality and CCG name to the DA table, but I'm not having much luck. I have a dashboard where a number of metrics is calculated over these dimensions and the problem with DA table is that when I select a SiteType I don't know what Locality and CCG the associated PracticeDI belongs to.

Any ideas?

[#2]

DA:

LOAD * INLINE [

    PracticeDI, SiteType

    Bewick Crescent Surgery, Practice

    Bewick Road Surgery, Practice

    Birtley Medical Group, Practice

    Blacketts Medical Practice, Practice

    CareFirst, CCG

    Churchill Medical Centre, Practice

    Holmside Medical Group, Practice

    Sedbergh Health Centre, Practice

];

GA:

LOAD * INLINE [

    SiteType, Practice, Locality, CCG

    CCG, CareFirst, None, CareFirst

    Practice, Bewick Crescent Surgery, Sedgefield Locality, DDES CCG

    Practice, Bewick Road Surgery, None, CBC

    Practice, Birtley Medical Group, None, CBC

    Practice, Blacketts Medical Practice, None, Darlington CCG

    Practice, Churchill Medical Centre, None, Kingston CCG Central Office

    Practice, Holmside Medical Group, None, Newcastle West Clinical Commissioning Group

    Practice, Sedbergh Health Centre, None, Cumbria

];

Does this work for you?

[#3]

I wouldn't be able to use that in my script. I'm after a mapping load...

[its_anandrjs]

Try with the mapping load

DAMap:

Mapping LOAD * INLINE [

    Practice, SiteType

    Bewick Crescent Surgery, Practice

    Bewick Road Surgery, Practice

    Birtley Medical Group, Practice

    Blacketts Medical Practice, Practice

    CareFirst, Ccg

    Churchill Medical Centre, Practice

    Holmside Medical Group, Practice

    Sedbergh Health Centre, Practice

];

GA:

LOAD *,ApplyMap('DAMap',Practice) as PracticeDesc;

LOAD * Inline

  [

    SiteTypeID, Practice, Locality, CCG

    CCG, CareFirst, None, CareFirst

    Practice, Bewick Crescent Surgery, Sedgefield Locality, DDES CCG

    Practice, Bewick Road Surgery, None, CBC

    Practice, Birtley Medical Group, None, CBC

    Practice, Blacketts Medical Practice, None, Darlington CCG

    Practice, Churchill Medical Centre, None, Kingston CCG Central Office

    Practice, Holmside Medical Group, None, Newcastle West Clinical Commissioning Group

    Practice, Sedbergh Health Centre, None, Cumbria

];

[Colin-Albert · Accepted solution]

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

DA_Map:

Mapping

LOAD * INLINE [
    PracticeDI, SiteTypeDI
    Bewick Crescent Surgery, Practice
    Bewick Road Surgery, Practice
    Birtley Medical Group, Practice
    Blacketts Medical Practice, Practice
    CareFirst, Ccg
    Churchill Medical Centre, Practice
    Holmside Medical Group, Practice
    Sedbergh Health Centre, Practice
];

Locality_Map:

Mapping

LOAD * INLINE [
    Practice, Locality
     Bewick Crescent Surgery, Sedgefield Locality
];

CCG_Map:

Mapping

LOAD * INLINE [
    Practice,  CCG
     CareFirst,  CareFirst
     Bewick Crescent Surgery,  DDES CCG
     Bewick Road Surgery,  CBC
     Birtley Medical Group,  CBC
     Blacketts Medical Practice,  Darlington CCG
     Churchill Medical Centre,  Kingston CCG Central Office
     Holmside Medical Group,  Newcastle West Clinical Commissioning Group
     Sedbergh Health Centre,  Cumbria
];

GA:

LOAD Practice,

Applymap('DA_Map', Practice) as SiteType,

Applymap('Locality_Map', Practice, 'None' ) as Lacality,

Applymap('CCG_Map', Practice ) as CCG

INLINE [
    Practice
     CareFirst
     Bewick Crescent Surgery
     Bewick Road Surgery
     Birtley Medical Group
     Blacketts Medical Practice
     Churchill Medical Centre
     Holmside Medical Group
     Sedbergh Health Centre
];

[maxgro]

DA:

LOAD * INLINE [

    PracticeDI, SiteTypeDI

    Bewick Crescent Surgery, Practice

    Bewick Road Surgery, Practice

    Birtley Medical Group, Practice

    Blacketts Medical Practice, Practice

    CareFirst, CCG

    Churchill Medical Centre, Practice

    Holmside Medical Group, Practice

    Sedbergh Health Centre, Practice

];

left join (DA) load

SiteType as SiteTypeDI,

Practice as PracticeDI,

Locality,

CCG;

//GA:

LOAD * INLINE [

    SiteType, Practice, Locality, CCG

    CCG, CareFirst, None, CareFirst

    Practice, Bewick Crescent Surgery, Sedgefield Locality, DDES CCG

    Practice, Bewick Road Surgery, None, CBC

    Practice, Birtley Medical Group, None, CBC

    Practice, Blacketts Medical Practice, None, Darlington CCG

    Practice, Churchill Medical Centre, None, Kingston CCG Central Office

    Practice, Holmside Medical Group, None, Newcastle West Clinical Commissioning Group

    Practice, Sedbergh Health Centre, None, Cumbria

];

[maxgro]

or this?

GA:
LOAD * INLINE [
    SiteType, Practice, Locality, CCG
    CCG, CareFirst, None, CareFirst
    Practice, Bewick Crescent Surgery, Sedgefield Locality, DDES CCG
    Practice, Bewick Road Surgery, None, CBC
    Practice, Birtley Medical Group, None, CBC
    Practice, Blacketts Medical Practice, None, Darlington CCG
    Practice, Churchill Medical Centre, None, Kingston CCG Central Office
    Practice, Holmside Medical Group, None, Newcastle West Clinical Commissioning Group
    Practice, Sedbergh Health Centre, None, Cumbria
];

MAP_GA_1:
mapping load
SiteType & '-' & Practice,
CCG
Resident GA;

MAP_GA_2:
mapping load
SiteType & '-' & Practice,
Locality
Resident GA;

DA:
load
  PracticeDI,
  SiteTypeDI,
  ApplyMap('MAP_GA_1', SiteTypeDI & '-' & PracticeDI) as NewField1,
  ApplyMap('MAP_GA_2', SiteTypeDI & '-' & PracticeDI) as NewField2;
LOAD * INLINE [
    PracticeDI, SiteTypeDI
    Bewick Crescent Surgery, Practice
    Bewick Road Surgery, Practice
    Birtley Medical Group, Practice
    Blacketts Medical Practice, Practice
    CareFirst, CCG
    Churchill Medical Centre, Practice
    Holmside Medical Group, Practice
    Sedbergh Health Centre, Practice
];

drop table GA;

[#8]

Witaj,

czy problem rozwiązany??

pozdrawiam

DM

[#9]

Hi Katarzyna

Why dont  you want to link the tables using practise?

* INLINE [

    Practice, SiteTypeDI

    Bewick Crescent Surgery, Practice

    Bewick Road Surgery, Practice

    Birtley Medical Group, Practice

    Blacketts Medical Practice, Practice

    CareFirst, Ccg

    Churchill Medical Centre, Practice

    Holmside Medical Group, Practice

    Sedbergh Health Centre, Practice

];





GA:

LOAD * INLINE [

    SiteType, Practice, Locality, CCG

    CCG, CareFirst, None, CareFirst

    Practice, Bewick Crescent Surgery, Sedgefield Locality, DDES CCG

    Practice, Bewick Road Surgery, None, CBC

    Practice, Birtley Medical Group, None, CBC

    Practice, Blacketts Medical Practice, None, Darlington CCG

    Practice, Churchill Medical Centre, None, Kingston CCG Central Office

    Practice, Holmside Medical Group, None, Newcastle West Clinical Commissioning Group

    Practice, Sedbergh Health Centre, None, Cumbria

];

Thanks and regards

Padma

[#10]

Tak Dariusz, bledem okazal sie niewlasciwy mapping load, bardzo czesto rozwiazanie samo wpada do glowy po opublikowaniu problemu wlasnie tutaj

pozdrawiam Kasia