field converted to a table

[#1]

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

[#2]

FacID is in the data just cut it out in error

[Vegar]

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.)

[sunny_talwar]

I think vegar.lie.arntsen‌ 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

[#5]

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

[Aurelien_Martinez]

Hi,

For i=1 to FieldValueCount('Titles')

For i=1 to FieldValueCount('Notes')

[#7]

I get the same error if I leave it as Titles or Notes

Do not understand script line error

[#8]

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

[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

[rupamjyotidas]

Add this

LET a = NoOfRows('FinalTable1');

For i = 1 to $(a)

--- your script here----