Load field

[Anonymous]

Hi all,

I'm trying to load a field if it contains certain information.

I know wildmatch loads values that contain certain information but I'm wondering if there is a way of loading a field if it contains certain information.

For example something like

Load

*

From Data Source

Where wildmatch(*,'Discuss Quote')

This would then load any fields that contain the words 'Discuss Quote' in it's row.

I will then do a crosstable to group these fields together into one field.

Thanks

Gareth

stevedark‌ hic‌ rwunderlich‌

[Anonymous]

Thanks Steve,

I mean the contents of that field. So if the contents of any field contains 'Discuss Quote' load this. I can then add these fields that meet this criteria into a crosstable to lump them all into one field.

I hope I'm making sense!

Thanks

Gareth

[stevedark · Accepted solution]

Hi Gareth,

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

tmpTable:

CROSSTABLE(Header,Value,1)

LOAD

  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

tmpHeaders:

LOAD DISTINCT

   Header as ExistsHeader

WHERE WildMatch(Value, '*Discuss Quote*');

// Load all the data in any column which you are interested in

Table:

NOCONCATENATE LOAD

  Header,

  Value

WHERE EXISTS (ExistsHeader, Header)

;

// Clean up

DROP TABLE tmpTable;

DROP TABLE tmpHeaders;

Hope that helps.

Steve

[Anonymous]

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.

See below

TempTransactions:

CrossTable(Temp1,Transactions)

Load

*

FROM

[\\dcn3pfsh104\home_1\CYDU\Desktop\Webchat\Webchat*.xlsx]

(ooxml, embedded labels, table is [Data])

;

DROP Field Temp1;

Transactions:

NoConcatenate Load

*

Resident TempTransactions

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!