Loop through a table to check which column has the value

[fishing_weights]

I have a table with 100 columns

I want to check for column 1 = 'ID001' if the other 99 columns has a certain date value say '2025-01-01'

But my code is throwing the error fieldname not found in the for loop...

what i did

// first filter column 1 for ID001

[TEST]: NoConcatenate LOAD Distinct * Resident[MAIN_TABLE] where column1='ID001';

// Loop to check for date value =  '2025-01-01' if have flag as 1

For f = 1 to NoOfFields('TEST')
LET F1 = FieldName($(f), 'TEST');
[Fields]:
Load distinct FieldName($(f), 'TEST') as Field, If(Date(Floor($(F1)))=Date(Floor(' 2025-01-01')),1,null()) as check
AutoGenerate 1;
Next f
DROP Table[TEST];

Any idea how to solve? Thanks in advance!

[mpc]

Hi,

Did you try to put $(F1) between single quote like this:

Load distinct FieldName($(f), 'TEST') as Field, If(Date(Floor('$(F1)'))=Date(Floor(' 2025-01-01')),1,null()) as check

Kind regards

[fishing_weights]

thanks that works! however its not checking against the row value but the field name itself. anyway to make it check the row values within the column?

[mpc]

You will need to use the Peek() function then:

Let vValue = Peek('$(F1)');

Load distinct FieldName($(f), 'TEST') as Field, If(Date(Floor('$(vValue)'))=Date(Floor(' 2025-01-01')),1,null()) as check;

[Levi_Turner]

I am not entirely following the ask. I think it's something like this:

id	column1	column2	column3	column4
1	ID001	A	2025-01-01	2022-01-02
2	ID001	B	2025-01-01	2025-01-01
3	ID001	B	2022-01-02	2022-01-02
4	ID002	C	2025-01-01	2025-01-01

For this sample data set, you want to filter on column1 = ID001 then iterate over all columns to determine if any contain the value 2025-01-01. In this example you would flag id = 1 (column3 = 2025-01-01) and id 2 (column3 and column4 = 2025-01-01).

You didn't state that you need to keep track of where the flag originated from which is a key detail. Let's assume that you don't. If so then here's the approach that comes to mind for me:

// Load my table
[t1]:
LOAD * INLINE [
id,column1,column2,column3,column4
1,ID001,A,2025-01-01,2022-01-02
2,ID001,B,2025-01-01,2025-01-01
3,ID001,B,2022-01-02,2022-01-02
4,ID002,C,2025-01-01,2025-01-01
];

// Filter out the column1 rows I don't want
[t2]:
NoConcatenate
LOAD
	[id]
RESIDENT [t1] WHERE [column1] = 'ID001';

// For each field, check if the field = '2025-01-01' and name the field fieldname_check, set the value to '1' where true and null where not true
FOR f = 1 TO NoOfFields('t1')
	LET F1 = FieldName($(f), 't1');
	LEFT JOIN([t2])
	LOAD
		[id],
	    IF($(F1) = '2025-01-01',1,Null()) AS $(F1)_check
	RESIDENT [t1];
    // maybe WHERE clause
NEXT f

// Concat the _check columns together to use in an expression later. End result is 'field1_check,field2_check,...'
LET v_expression = '';
FOR f = 1 TO NoOfFields('t2')
	LET F1 = FieldName($(f), 't2');
    // If field name is not my id field (used to join back to the original table), then concat the field name
	LET v_expression = IF('$(F1)' <> 'id','$(v_expression)' & '$(F1)' & ',',Null());
NEXT f

// Since the last field from above will end with a comma (,), remove this comma
LET v_expression = Left('$(v_expression)', Len('$(v_expression)') - 1);

// Join back to original table
LEFT JOIN([t1])
LOAD
	[id],
    Sign(RangeSum($(v_expression)))  AS [check] // We are summing all fields then using the SIGN() function to return a 1 or 0. 1 = any positive sum, 0 = no field = '2025-01-01'
RESIDENT [t2];

DROP TABLE [t2];

Resulting in this:

For the v_expression variable, we need to have an unique identifier for the original table (so I can join it back into the original table. I called it id. If you have a different field name, then adjust the IF clause to that field name.