Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

field converted to a table

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

Tags (1)
1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: field converted to a table

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

13 Replies
Not applicable

Re: field converted to a table

FacID is in the data just cut it out in error

Vegar
Not applicable

Re: field converted to a table

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

Please ekskuse my Norglish and Swenglish typos.
sunny_talwar
Not applicable

Re: field converted to a table

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

Not applicable

Re: field converted to a table

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

amartinez35
Not applicable

Re: field converted to a table

Hi,

For i=1 to FieldValueCount('Titles')

For i=1 to FieldValueCount('Notes')

Not applicable

Re: field converted to a table

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

Do not understand script line error

Not applicable

Re: field converted to a table

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
Not applicable

Re: field converted to a table

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
Not applicable

Re: field converted to a table

Add this

LET a = NoOfRows('FinalTable1');

For i = 1 to $(a)

--- your script here----