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 |
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
Jumped the gun a little.
Multiple rows I need them all on one row
Can you create a table with all these fields and share a screenshot?
FacID, CFID, [CFType], [CFDataType], CFRequired, Capacity, ERX Facility
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
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
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
No they are still on multiple rows.
Only one row per FacID
I guess remove this fields from the table and then check. Those are making it show you multiple rows.
Nice, thank you for your help, greatly appreciate it.