Load Script Loop through table creating new column from value

[#1]

Hi all,

Trying to get this to work.

TTable:
LOAD * INLINE [
    Pol, TypeCat, Value
1, Cov A, 100,
1, Cov B, 500,
1, Cov C, 600,
1, Cov D, 700,
1, Form, 2,
1, Doc, F
];
PMergeTo:
Load
Pol
Resident TTable;

Let RCount=NoOfRows('TTable');
For i=1 to $(RCount)
 Let FieldName=FieldValue('TypeCat',$(i));
 
 THolding:
 Load 
  Pol,
  Value As $(FieldName)  
 Resident TTable
 Where TypeCat=$(FieldName);
 
 Left JOIN (PMergeTo) LOAD * RESIDENT THolding;
 Drop Table THolding; 
 
Next i;
Drop Table TTable;

Getting this error, I must be missing something.

Thank you for your help.

[#2]

...  Value As $(FieldName)  .... this part of script is wrong

each step your column name is different? that's what you want to get?

[#3]

My goal is to horizontalize the table by the column "Pol", creating a column for each distinct value in "TypeCat" then putting the value from "Value" into it.

Pol CovA CovB CovC CovD Form Doc

1    100    500   600   700   2       F

So I was trying to loop through and do 1 column at a time, then join it to the table of Pols.

In my non dummy data I have several hundred thousand rows, I want to do it through code because new values of "TypeCat" come into the data.

[jonathandienst]

Hi

Your target field names have spaces in them, so they need to be marked off with []:

THolding:
Load
  Pol,
  Value As [$(FieldName)] 
Resident TTable
Where TypeCat=[$(FieldName)];

I see what you are trying to do and it looks like it should work. Some suggestions:

• put a DISTINCT in the PMergeTo load statement. Duplicates will cause problems
• If you loop 1 to NoOfRows(), use Peek('fieldname', i-1, 'tablename') to get the values. Or if you want to use FieldValue('fieldname', i), loop 1 to FieldValueCount(f'ieldname')

Good luck

Jonathan

[#5]

Jonathan,

I really appreciate you taking the time to provide feedback.

I noticed "FieldName" is actually the name of a script function in Qlikview, so I changed the name of the variable to "ValueOfField".

Additionally I put ' around my use of the variable.

THolding:

Load

  Pol,

  Value As '$(ValueofField)' 

Resident TTable

Where TypeCat='$(ValueofField)';

Works like a dream.

I'll tag your response as "Helpful" so you get points.

[CELAMBARASAN]

Hi,

THolding:

Load

  Pol,

  Value As $(ValueofField)

Resident TTable

Where TypeCat='$(ValueofField)';

This is correct one. no need quotes in bold part

because quotes is to tell it as string. for field names no need of it