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

[swuehl]

Maybe have a look at

The Generic Load

[sunny_talwar]

This might be another way to do it

Table:

LOAD * INLINE [

    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

];

FinalTable:

LOAD Distinct FacID

Resident Table;

For i = 1 to FieldValueCount('Field Name')

  LET vField = FieldValue('Field Name', $(i));

  Left Join (FinalTable)

  LOAD FacID,

  [Field Value] as [$(vField)]

  Resident Table

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

NEXT

DROP Table Table;

[#4]

I am using a direct connect.

[FacilityCustomFields]:
LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];
LOAD [FacID],
[CFID],
[CFValue];
SQL SELECT  "FacID",
  "CFID",
  "CFValue"
FROM "Fac"."dbo"."FacilityCustomFields";

CustomFieldDefs]:
LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];
LOAD [CFID],
[CFType],
    [CFName],
[CFDataType],
[CFRequired];
SQL SELECT  "CFID",
  "CFType",
  "CFName",
  "CFDataType",
  "CFRequired"
FROM "FwLTC"."dbo"."CustomFieldDefs";

[#5]

I tried to put the Generic Load in a few different places but keeps kicking out.

I think my problem is that they are not all in one table.

[sunny_talwar]

How are these fields corresponds to the sample? What is Field Value and what is Field Name?

[#7]

CFName  is field Name

CFValue  is Field value

[sunny_talwar]

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,

          CFID,

          [CFType],

          [CFDataType],

          CFRequired

Resident FacilityCustomFields;

For i = 1 to FieldValueCount('CFName')

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

  Left Join (FinalTable)

  LOAD FacID,

            CFID,

            [CFType],

            [CFDataType],

            CFRequired,

            [Field Value] as [$(vField)]

  Resident FacilityCustomFields

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

NEXT

DROP Table FacilityCustomFields;

[#9]

I have had this before I know I am missing something but what ?

[FacilityCustomFields]:
LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];
LOAD [FacID],
[CFID],
[CFValue];
SQL SELECT  "FacID",
  "CFID",
  "CFValue"
FROM "Fac"."dbo"."FacilityCustomFields";

Join (FacilityCustomFields)

[CustomFieldDefs]:
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,
          CFID,
         [CFType],
          [CFDataType],
          CFRequired

Resident FacilityCustomFields;

For i = 1 to FieldValueCount('CFName')

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

  Left Join (FinalTable)

  LOAD FacID,
            CFID,
            [CFType],
            [CFDataType],
            CFRequired,
            [Field Value] as [$(vField)]
  Resident FacilityCustomFields
  Where [Field Name] = '$(vField)';

NEXT

[sunny_talwar · Accepted solution]

Not exactly sure, but try removing these and see if it makes any difference

[FacilityCustomFields]:
LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];
LOAD [FacID],
[CFID],
[CFValue];
SQL SELECT  "FacID",
  "CFID",
  "CFValue"
FROM "Fac"."dbo"."FacilityCustomFields";

Join (FacilityCustomFields)

[CustomFieldDefs]:
LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];
LOAD [CFID],
  [CFType],
    [CFName],
  [CFDataType],
  [CFRequired];
SQL SELECT  "CFID",
    "CFType",
  "CFName",
  "CFDataType",
    "CFRequired"
FROM "FwLTC"."dbo"."CustomFieldDefs";