20 Replies Latest reply: Oct 21, 2016 1:08 PM by Sunny Talwar RSS

    field with multiple names

    Robert Winkel

      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

       

       

         

      FacIDField ValueField Name
      home 3155 Capacity
      home 312-15-15 PCCERX Facility
      home 307-25-16V2016 (MM-DD-YY)
      home 3 V2016 Clarification Needed
      Home1164Capacity
      Home110-06-15 PCCERX Facility
      Home108-31-16V2016 (MM-DD-YY)
      Home1 V2016 Clarification Needed
      Home2140Capacity
      Home212-01-15 PCCERX Facility
      Home207-14-16V2016 (MM-DD-YY)
      Home2 V2016 Clarification Needed
      home4117Capacity
      home411-15-16 PCCERX Facility
      home408-31-16V2016 (MM-DD-YY)
      home4 V2016 Clarification Needed
      Home5197Capacity
      Home5PCCERX Facility
      Home5 V2016 (MM-DD-YY)
      Home5 V2016 Clarification Needed
      Need to display like this below.
      FacIDCapacityERX FacilityV2016 (MM-DD-YY)V2016 Clarification Needed
      home 3155 12-15-15 PCC07-25-16
      Home116410-06-15 PCC08-31-16
      Home214012-01-15 PCC07-14-16
      home411711-15-16 PCC08-31-16
      Home5197PCC

       

        • Re: field with multiple names
          Stefan Wühl

          Maybe have a look at

          The Generic Load

          • Re: field with multiple names
            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;


            Capture.PNG

              • Re: field with multiple names
                Robert Winkel

                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";

                  • Re: field with multiple names
                    Sunny Talwar

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

                      • Re: field with multiple names
                        Robert Winkel

                        CFName  is field Name

                        CFValue  is Field value

                          • Re: field with multiple names
                            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;

                              • Re: field with multiple names
                                Robert Winkel

                                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

                                  • Re: field with multiple names
                                    Sunny Talwar

                                    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";

                                      • Re: field with multiple names
                                        Robert Winkel

                                        Jumped the gun a little.

                                        Multiple rows I need them all on one row

                                          • Re: field with multiple names
                                            Sunny Talwar

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

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

                                                • Re: field with multiple names
                                                  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

                                                    • Re: field with multiple names
                                                      Robert Winkel

                                                      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

                                                       

                                                       

                                                       

                                                       

                                                       

                                • Re: field with multiple names
                                  dixit panchal

                                  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