Are you looking to get this?
LOAD 1 as Temp,
LOAD * INLINE [
Header 1, Header 2, Header 3
Discuss Quote, Discuss Query, Stop Payment
Discuss Problem, Discuss Quote, Hold Payment
Right Join (Table)
LOAD Distinct Header
Where WildMatch(Value, '*Discuss Quote*');
294271.qvw 148.0 K
If I understand your question correctly, I think you would need a number of OR statements on the WHERE to check each field in turn for the content you are looking for.
I would agree with stalwar1 that doing the CrossTable first and then doing a WHERE on a RESIDENT load of that table may make more sense.
When you say the "field contains a string" do you mean the field name, or the contents of that field in any row?
You could do a bit of code that loads the field list for the table and then loads distinct for each column with the where statement to then get the NoOfRows for that temporary table, and then build out the load script (list of fields) into a variable.
This feels like it would be far less efficient than having the AND statements on the WHERE.
If the issue is that you don't know what the fields are then there are ways of approaching this, which will differ depending on the data source.
stalwar1 's solution above is spot on. It makes far more sense to do the cross table first and then exclude the rows. You don't need to know the column headers as you can load with *, so:
// Load all data into crosstable
1 as DummyField, // not sure if you can omit this and have ,0 on the CROSSTABLE statement
FROM [your data source]
// Get list of headers you are interested in
Header as ExistsHeader
WHERE WildMatch(Value, '*Discuss Quote*');
// Load all the data in any column which you are interested in
WHERE EXISTS (ExistsHeader, Header)
// Clean up
DROP TABLE tmpTable;
DROP TABLE tmpHeaders;
Hope that helps.
Thanks for all your suggestions guys, I managed to build something that is very similar to stevedark suggestion. So I'm basically creating a new field (Transactions) then only loading data that contains discuss quote/amend policy etc and so long as I drop the original TempTransactions table it will only load the data I want.
(ooxml, embedded labels, table is [Data])
DROP Field Temp1;
Where Transactions = 'Discuss Quote' or Transactions = 'Amend/Discuss Policy' or Transactions = 'Cancel Policy' or Transactions = 'Renew Policy' or Transactions = 'Ghost Chat' or Transactions = 'Transfer Chat';
DROP Table TempTransactions;
Thanks again everyone!