13 Replies Latest reply: Nov 4, 2016 10:55 AM by Sunny Talwar RSS

    field converted to a table

    Robert Winkel

      I am trying to convert a field (Title) to a table  I can get the first item but then it rejects?

      What am I missing?

       

       

       

      [FacContacts]:
      LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];
      LOAD [ConID],
        [Title],
        [Notes];
      SQL SELECT  "ConID",
         "Title",
         "Notes"
      FROM "Fac"."dbo"."FacContacts";

       

      FinalTable1:

      LOAD Distinct FacID

      Resident FacContacts;

      For i = 1 to FieldValueCount('Title')

        LET vField1 = FieldValue('Title', $(i));

        Left Join (FinalTable1)

         LOAD FacID,
                   ['Title'] as [$(vField1)]
         Resident FacContacts

         Where ['Title'] = '$(vField1)';

      NEXT

        • Re: field converted to a table
          Robert Winkel

          FacID is in the data just cut it out in error

          • Re: field converted to a table
            Vegar Lie Arntsen

            My guess is that you dont have a field called ['Title'], try with [Title] instead, without the ' '.

            (To check which data you have in datmodel after the SQL Select you could temporary add an exit script before loading resident.)

              • Re: field converted to a table
                Robert Winkel

                Ok I tried putting the brackets around the, and now I get Script line error.

                Does it matter if this is the second process to take a field and turn it into a table?

                First one below works fine

                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

                 

                 

                 

                 

                [FacContacts]:
                LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];
                LOAD [ConID],
                [FacID],
                [Title],
                  [Notes];
                SQL SELECT  "ConID",
                  "FacID",
                  "Title",

                  "Notes"
                FROM "Fac"."dbo"."FacContacts"


                   FinalTableA:

                   LOAD Distinct FacID
                   Resident FacContacts;
                   For i = 1 to FieldValueCount([Title])
                     LET vField1 = FieldValue(Title, $(i));

                     Left Join (FinalTableA)

                       LOAD FacID,
                                 [Title] as [$(vField1)]
                       Resident FacContacts

                       Where [Title] = '$(vField1)';

                    NEXT

                  • Re: field converted to a table
                    Sunny Talwar

                    You need single quotes around the field name and not square brackets

                     

                    FinalTableA:

                      LOAD Distinct FacID
                      Resident FacContacts;
                      For i = 1 to FieldValueCount('Title')
                        LET vField1 = FieldValue('Title', $(i));

                        Left Join (FinalTableA)

                          LOAD FacID,
                                    [Title] as [$(vField1)]
                          Resident FacContacts

                          Where [Title] = '$(vField1)';

                    NEXT

                      • Re: field converted to a table
                        Robert Winkel

                        Its filling in the field heading instead of the actual data?

                         

                          • Re: field converted to a table
                            Sunny Talwar

                            I wish you saw my earlier response carefully and you would not have needed to go through all this trouble . Try this:

                             

                            FinalTableA:

                              LOAD Distinct FacID
                              Resident FacContacts;
                              For i = 1 to FieldValueCount('Title')
                                LET vField1 = FieldValue('Title', $(i));

                                Left Join (FinalTableA)

                                  LOAD FacID,
                                            Notes as [$(vField1)]
                                  Resident FacContacts

                                  Where [Title] = '$(vField1)';

                            NEXT

                             

                            Please note that I have changed Title to Notes and also used the single quotes within my FieldValueCount() and FieldValue() functions.

                             

                            HTH

                             

                            Best,
                            Sunny

                              • Re: field converted to a table
                                Robert Winkel

                                I did try it that way and got this.  so then I tried a few variations trying to see what works and what don't.

                                I always copy and paste you give me because I know this is extremely sensitive and can be done in multiple ways. 

                                 

                                 

                                  • Re: field converted to a table
                                    Sunny Talwar

                                    I think what we have is going in the right direction, we might just need to figure our if Notes field have any value in it. Have you checked the output of this table?

                                     

                                    [FacContacts]:

                                    LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];

                                    LOAD [ConID],

                                    [FacID],

                                    [Title],

                                      [Notes];

                                    SQL SELECT  "ConID",

                                      "FacID",

                                      "Title",

                                     

                                      "Notes"

                                    FROM "Fac"."dbo"."FacContacts";

                                     

                                    If you run this and create a table object with ConID, FacID and Title and Notes as dimension, what do you see?

                        • Re: field converted to a table
                          Sunny Talwar

                          I think Vegar is right, but in addition it seems that you also might need to use notes as [$(vField1)] instead of Title as [$(vField1)]

                           

                          [FacContacts]:
                          LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];
                          LOAD [ConID],
                            [Title],
                            [Notes];
                          SQL SELECT  "ConID",
                            "Title",
                            "Notes"
                          FROM "Fac"."dbo"."FacContacts";

                           

                          FinalTable1:

                          LOAD Distinct FacID

                          Resident FacContacts;


                          For i = 1 to FieldValueCount('Title')

                            LET vField1 = FieldValue('Title', $(i));

                           

                            Left Join (FinalTable1)

                            LOAD FacID,
                                      Notes as [$(vField1)]
                            Resident FacContacts

                            Where Title = '$(vField1)';

                          NEXT

                          • Re: field converted to a table
                            Robert Winkel

                             

                            FinalTable1:

                              LOAD Distinct FacID
                              Resident FacContacts;
                              For i = 1 to FieldValueCount(Notes)
                                LET vField1 = FieldValue(Title, $(i));

                                Left Join (FinalTable1)

                                  LOAD FacID,
                                            Notes as [$(vField1)]
                                  Resident FacContacts

                                  Where Title = '$(vField1)';

                               NEXT