Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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";
Maybe have a look at
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;
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";
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.
How are these fields corresponds to the sample? What is Field Value and what is Field Name?
CFName is field Name
CFValue is Field value
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;
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
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";