field with multiple names

[#1]

Below PIC one is what I have.

Each FacID has multiple rows

I actually use SQL to get my data.

Can I have a field multiple times and limit the field by the data in the field?

Like Fieldname = capacity as Capacity

        Fieldname = ERX Facility  as ERX Facility

FacID	Field Value	Field Name
home 3	155	Capacity
home 3	12-15-15 PCC	ERX Facility
home 3	07-25-16	V2016 (MM-DD-YY)
home 3		V2016 Clarification Needed
Home1	164	Capacity
Home1	10-06-15 PCC	ERX Facility
Home1	08-31-16	V2016 (MM-DD-YY)
Home1		V2016 Clarification Needed
Home2	140	Capacity
Home2	12-01-15 PCC	ERX Facility
Home2	07-14-16	V2016 (MM-DD-YY)
Home2		V2016 Clarification Needed
home4	117	Capacity
home4	11-15-16 PCC	ERX Facility
home4	08-31-16	V2016 (MM-DD-YY)
home4		V2016 Clarification Needed
Home5	197	Capacity
Home5	PCC	ERX Facility
Home5		V2016 (MM-DD-YY)
Home5		V2016 Clarification Needed
Need to display like this below.
FacID	Capacity	ERX Facility	V2016 (MM-DD-YY)	V2016 Clarification Needed
home 3	155	12-15-15 PCC	07-25-16
Home1	164	10-06-15 PCC	08-31-16
Home2	140	12-01-15 PCC	07-14-16
home4	117	11-15-16 PCC	08-31-16
Home5	197	PCC

[Anonymous]

hi

Try this:

Name_Mapping:

Mapping

LOAD * INLINE [

    Name, Name_New

    Capacity, Capacity

    capacity, Capacity

    CapaCity, Capacity

];

in original Table

ApplyMap('Name_Mapping',Name)

as Capacity;

try this way it will worked

Regards,

Dixit Panchal

[#3]

Jumped the gun a little.

Multiple rows I need them all on one row

[sunny_talwar]

Can you create a table with all these fields and share a screenshot?

FacID, CFID, [CFType], [CFDataType], CFRequired, Capacity, ERX Facility

[#5]

[sunny_talwar]

So it seems that you data looks like this to begin with

FacID,     CFID,     CFType,      CFDataType,    CFRequired,      CFName,   CFValue

WTFD,     58,     F,                         N,                -1,        Capacity,     141

WTFD,     59,     F,                         X,                0,          ERX Facility, HealthMEDX

So, CFID and other information is slightly different for each row and you are looking to group it by FacID? Is that true?

Then may be try this:

[FacilityCustomFields]:

LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];

LOAD [FacID],

[CFID],

[CFValue];

SQL SELECT  "FacID",

  "CFID",

  "CFValue"

FROM "Fac"."dbo"."FacilityCustomFields";

Join (FacilityCustomFields)

LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];

LOAD [CFID],

[CFType],

    [CFName],

[CFDataType],

[CFRequired];

SQL SELECT  "CFID",

  "CFType",

  "CFName",

  "CFDataType",

  "CFRequired"

FROM "FwLTC"."dbo"."CustomFieldDefs";

FinalTable:

LOAD Distinct FacID

Resident FacilityCustomFields;

For i = 1 to FieldValueCount('CFName')

  LET vField = FieldValue('CFName', $(i));

  Left Join (FinalTable)

  LOAD FacID,

            [Field Value] as [$(vField)]

  Resident FacilityCustomFields

  Where [Field Name] = '$(vField)';

NEXT

[#7]

CFID is a ID the software assigns to the CFName

So, CFID and other information is slightly different for each row and you are looking to group it by FacID? Is that true? TRUE

  Left Join (FinalTable)

  LOAD FacID,

            [Field Value] as [$(vField)]       *************  CHANGED THE FIELD VALUE TO CFVALUE

  Resident FacilityCustomFields

  Where [Field Name] = '$(vField)'      ***************   CHANGED THE FIELD NAME TO CFNAME

If I did not change it it said field not found

[FacilityCustomFields]:

LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];

LOAD [FacID],
[CFID],
[CFValue];
SQL SELECT  "FacID",
  "CFID",
  "CFValue"
FROM "Fac"."dbo"."FacilityCustomFields";

Join (FacilityCustomFields)

// LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];

LOAD [CFID],
[CFType],
    [CFName],
[CFDataType],
[CFRequired];
SQL SELECT  "CFID",
  "CFType",
  "CFName",
  "CFDataType",
  "CFRequired"
FROM "FwLTC"."dbo"."CustomFieldDefs";

FinalTable:

LOAD Distinct FacID

Resident FacilityCustomFields;

For i = 1 to FieldValueCount('CFName')

  LET vField = FieldValue('CFName', $(i));

  Left Join (FinalTable)

  LOAD FacID,
            [CFValue] as [$(vField)]     
  Resident FacilityCustomFields

  Where [CFName] = '$(vField)';  

NEXT

[sunny_talwar]

Yes, I forgot to change them. Does this give you the result you were looking for? I did not understand from your above response if you got what you wanted or if you still have issues

[#9]

No they are still on multiple rows.

Only one row per FacID

[sunny_talwar]

I guess remove this fields from the table and then check. Those are making it show you multiple rows.

[#11]

Nice, thank you for your help, greatly appreciate it.