For...Do While - For $(Var) = 1 error

[#1]

Hi

I am trying to create a loop which basically looks at the different values in a table (FieldName) and creates a field in another table for each of the values it finds.  I am simply using a Peek feature to look up the field value in the original table which populates a variable.  This variable then sets the name of the field in the following load.

However, I cannot get the "For Fieldno" to work it keeps complaining of an error.   Even when I force the Fieldno variable to be 1, the Let Fieldno = $(Fieldno) + 1 value does not  return a value from the field variable peek (there are 27 fields in total).

Can any one help?  Attached below is my code.

Thanks

Steve

Let fieldcount = Peek('FieldCount',-1, 'ComplaintsAreas');

//Let Fieldno = 1;
for Fieldno = 1
Do while $(Fieldno) <= $(fieldcount)
Let field = Peek('FieldName', '$(Fieldno)' , 'ComplaintsAreas');  

Trace '$(FieldName)';
Trace '$(FieldNo)';



ComplaintsOnly:
Load
DataID,
strValue as $(field)
Resident Complaints where FieldName = '$(field)';

Let Fieldno = $(Fieldno) + 1;

loop

[Nicole-Smith]

If you're using a for loop, you need to include a to and a next.

Example:

FOR a=1 to 9

LOAD * FROM file$(a).csv;

NEXT

[Gysbert_Wassenaar]

Can you explain what the purpose of it all is (for the end user interacting with the data)? I have the feeling that there probably is an easier way to achieve that result. If you could also post some sample data that would be helpful.

[#4]

I switched to use the For...Next functionality but with no joy.

I have attached an example, I am trying to create a new field for each value within a field.  i.e. if I have a field called data type and one value was Department, one value city etc.  I'd want a table with those values as fields.

Thanks in advance

Steve

[Gysbert_Wassenaar]

The question is still... why? What is the purpose? Why do you want a table like that? Why is a pivot table unsuitable?

What you say you want seems to be what a generic load does:

Generic

Data:

LOAD * INLINE [

    Company, DataType, Data

    CoA, Department, Sales

    CoA, Department, Marketing

    CoA, Department, Accounting

    CoA, City, Durham

    CoA, City, York

    CoA, City, Leeds

    CoA, Salesperson, Dave

    CoA, Salesperson, Gemma

    CoA, Salesperson, Sonia

];

[#6]

Hi

Thanks for your input so far.  Let me clarify with a more meaningful example (attached).

Basically, I have a table with three fields.  One is the ID of the data (or person in the example), the second field is the type of data (e.g. their name or email address etc.) and the third field is the actual data (e.g. Steve or steve@hotmail.com etc).

What I want from the attached table is a field for the ID (which I already have) and a field for Surname, a field for First Name, a field for email address and  a field for Postcode, thus I have 5 fields instead of 3.  I guess it's like a reverse of a Crosstable.

I have included an example of what I'm trying to do in the model.

Maybe I'm missing something obvious.....

Thanks for any help/assistance in advance.

Steve

[Gysbert_Wassenaar · Accepted solution]

Yup, sounds like you want a generic load like I posted above. See this (amazingly timed) blog post from Henric Cronström: The Generic Load.

[#8]

Ahh, the generic load.  I'd heard of this but never looked into it.

That was indeed a timely load.  Many thanks for your helps Gysbert.