How to handle bad product names

[#1]

Hey All,

Struggling to figure out how to create a clean products table that will link to customers. What I have is a table with one row per customer, so for companies that have multiple products (which is most of them), the products are all listed as a single record. 

ex.

Customer A ----> ,producta,productB,C,

Customer B ----> productb,A,ProC

We have a mapping table from CRM, but it doesnt work for multiple product customers. 

I tried creating a new table using wildmatch, but when I link that table to my customers table, its still only going to have one record per customer (realized this after a half hour of plugging in wildmatches).

Please Help!

[MK_QSL]

Use SubField () function...

[shree909]

Subfield function should solve your issue,

if possible could you post a sample file..

[Anonymous · Accepted solution]

If you are new to the Subfield function and you want this automated then the long answer is that you should next a subfield function into a loop. In this example we just loop approx 50 times to catch all possible entries....

TempTable:
LOAD

SubField(CustomerField,',',1) as Field1
FROM
Test.txt (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

For i = 2 to 50
Concatenate (TempTable)
LOAD SubField(CustomerField,',',$(i)) as Field1

FROM

Test.txt

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

next i

FinalCleanedTable:  // Clean by removing blanks (assuming there are other fields in the table)
NoConcatenate Load * Resident TempTable Where Len(CustomerField) > 0 ;
Drop Table TempTable ;

[#5]

That worked!  Thanks so much.