Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
FacID is in the data just cut it out in error
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.)
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
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
Hi,
For i=1 to FieldValueCount('Titles')
For i=1 to FieldValueCount('Notes')
I get the same error if I leave it as Titles or Notes
Do not understand script line error
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
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
Add this
LET a = NoOfRows('FinalTable1');
For i = 1 to $(a)
--- your script here----