How to iterate over fields in a table

[ritvik4BI]

Hi All,

I have a doubt. I have a table which has 4-5 fields. Now I have a requirement such that I have to loop over the table (field by field) and select only those fields which satisfy a specific condition.

How to do it using the for loop?

Thanks and Regards.

[Lauri · Accepted solution]

You can do something like this, replacing my Load statement with what you need to do:

For f = 1 to NoOfFields('mytable')

Fields:
Load distinct FieldName($(f), 'mytable') as Field
AutoGenerate 1;
Next f

[manoranjan_d]

Hi Lauri,

I am having one question , suppose if i have 5 fields, out of 5 fields , i need 1-3 fields  to be retrieved and 1-3 field values also to be retrieved  in same time. In the loop how we can achieve it?

[manoranjan_d]

Any qlik experty can help on this scenario

[Lauri]

You can select a specific field with IF, and specific values of that field with WHERE:

For f = 1 to NoOfFields('mytable')
Let n = FieldName($(f), 'mytable');

If '$(n)' = 'Fieldname1' Then

NewData:
Load '$(n)' as Field, $(n) as Value
Resident mytable
where $(n) = 'Value1';

End If
Next f

[manoranjan_d]

Hi Lauri,

I understood and tried with your script but this is not generating the  NewData table

mytable:
LOAD A,
B,
C,
D
FROM
[C:\Users\NM\Desktop\Desktop data\Scenarios\6Mar21\New Microsoft Excel Worksheet.xlsx]
(ooxml, embedded labels, table is Sheet1);

For f = 1 to NoOfFields('mytable')
Let n = FieldName($(f), 'mytable');

If '$(n)' = 'Fieldname1' Then
NoConcatenate
NewData:
Load '$(n)' as Field, $(n) as Value
Resident mytable
where $(n) = 'Value1';

End If
Next f

drop Table mytable;
EXIT Script;

[Lauri]

You need to use the field name and values that are in your data. For example:

mytable:
LOAD A,
B,
C,
D
FROM
[C:\Users\NM\Desktop\Desktop data\Scenarios\6Mar21\New Microsoft Excel Worksheet.xlsx]
(ooxml, embedded labels, table is Sheet1);

For f = 1 to NoOfFields('mytable')
Let n = FieldName($(f), 'mytable');

If '$(n)' = 'A' Then
NoConcatenate
NewData:
Load '$(n)' as Field, $(n) as Value
Resident mytable
where $(n) = 1;

End If
Next f

drop Table mytable;
EXIT Script;