topic Re: For...Do While - For $(Var) = 1 error in QlikView

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

Mon, 31 Mar 2014 15:12:41 GMT

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

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

Nicole-Smith — Mon, 31 Mar 2014 15:38:52 GMT

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

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

Gysbert_Wassenaar — Mon, 31 Mar 2014 15:52:13 GMT

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.

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

Mon, 31 Mar 2014 16:15:28 GMT

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

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

Gysbert_Wassenaar — Mon, 31 Mar 2014 16:34:16 GMT

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

];

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

Tue, 01 Apr 2014 06:46:23 GMT

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

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

Gysbert_Wassenaar — Tue, 01 Apr 2014 07:38:38 GMT

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.

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

Tue, 01 Apr 2014 08:30:57 GMT

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.