Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to load certain fields based on their fieldname.
I tried to do this with an if statement but I don't get it toe work.
It should be something like this:
LOAD
if(fieldname contains '*K01*') as fieldname
Does anyone has an idea how to apply conditional load based on fieldnames?
And you don't know the field names upfront?
Maybe there are better solutions to what you are trying to achieve at the end, for creating a filtered list of field names to load, you can try something like
// Only a dummy table to have something to play with
TABLE:
LOAD * INLINE [
Dimension, Metric 1, Metric 2
1, 1, 10
2, 2, 9
3, 3, 8
4, 4, 7
5, 5, 6
6, 6, 5
7, 7, 4
8, 8, 3
9, 9, 2
10, 10, 1
];
// Create a field with all field names
FIELDS:
CROSSTABLE (FieldName, Value) LOAD
1 as Dummy, *
Resident TABLE
where RecNo() =1;
DROP FIELDS Dummy, Value;
// Create a filtered list of the field names, embedded in [..] to handle spaces etc.
CONCAT:
LOAD '[' & CONCAT(If (FieldName Like 'Metric*', FieldName),'], [') & ']' as FilteredFields
RESIDENT FIELDS;
// Create a variable from that filtered table value
Let vFilteredFields = Peek('FilteredFields',0,'CONCAT');
// Finally load your filtered table from source, instead of a resident TABLE LOAD, you can load your data directly FROM your source here and in the FIELDS table
LOAD $(vFilteredFields) RESIDENT TABLE;
//Clean up
DROP TABLE TABLE, FIELDS, CONCAT;
And you don't know the field names upfront?
Maybe there are better solutions to what you are trying to achieve at the end, for creating a filtered list of field names to load, you can try something like
// Only a dummy table to have something to play with
TABLE:
LOAD * INLINE [
Dimension, Metric 1, Metric 2
1, 1, 10
2, 2, 9
3, 3, 8
4, 4, 7
5, 5, 6
6, 6, 5
7, 7, 4
8, 8, 3
9, 9, 2
10, 10, 1
];
// Create a field with all field names
FIELDS:
CROSSTABLE (FieldName, Value) LOAD
1 as Dummy, *
Resident TABLE
where RecNo() =1;
DROP FIELDS Dummy, Value;
// Create a filtered list of the field names, embedded in [..] to handle spaces etc.
CONCAT:
LOAD '[' & CONCAT(If (FieldName Like 'Metric*', FieldName),'], [') & ']' as FilteredFields
RESIDENT FIELDS;
// Create a variable from that filtered table value
Let vFilteredFields = Peek('FilteredFields',0,'CONCAT');
// Finally load your filtered table from source, instead of a resident TABLE LOAD, you can load your data directly FROM your source here and in the FIELDS table
LOAD $(vFilteredFields) RESIDENT TABLE;
//Clean up
DROP TABLE TABLE, FIELDS, CONCAT;
I have an additional question about your script.
Your suggestion is a smart solution and it works. So thanks for that.
But I need a little more complex add-on because I like to filter the fieldnames on their text 'format' instead of the text itself.
A simplified example of the targeted fieldnames look like these:
K01.01loremipsum
C12.21loremipsum
S03.12loremipsum
I would like to filter on the first 6 characters of the fieldname, where:
the first character is a letter,
the 2nd, 3rd, 5th and 6th is a number
the 3rd is a 'period'
Is it possible to add this to the filter commands your wrote on row 29?
You could try something like
CONCAT( If (isText(Left(FieldName,1)) AND isNum(Mid(FieldName,2,5), FieldName), ....
Thanks, it works like a charm now!
Your help is appreciated.