Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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?
Any qlik experty can help on this scenario
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
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;
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;