topic Re: Load Script Loop through table creating new column from value in QlikView

Load Script Loop through table creating new column from value

Thu, 12 Jul 2012 13:01:10 GMT

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.

Re: Load Script Loop through table creating new column from value

Thu, 12 Jul 2012 13:22:05 GMT

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

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

Re: Load Script Loop through table creating new column from value

Thu, 12 Jul 2012 14:30:22 GMT

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.

Re: Load Script Loop through table creating new column from value

jonathandienst — Thu, 12 Jul 2012 14:52:34 GMT

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

Re: Load Script Loop through table creating new column from value

Thu, 12 Jul 2012 15:44:38 GMT

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.

Re: Load Script Loop through table creating new column from value

CELAMBARASAN — Thu, 12 Jul 2012 20:15:29 GMT

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