Creating Records Looping Through Subfields

[m_perreault]

Hi All,

I have data such as below.

ID	Quotes
12AD5	MP: 100.87 || BD: 99 || SM: 101.03

I would like to turn this one record into three records such as below

ID	Source	Quote
12AD5	MP	100.87
12AD5	BD	99
12AD5	SM	101.03

Does anyone have any suggestions as to how to do this?

Thanks,
Mark

[petter · Accepted solution]

In a load script you could do it like this:

DATA:
LOAD
  ID,
  SubField(MoreRows,':',1) AS Source,
  SubField(MoreRows,':',2) AS Quote;
LOAD 
 ID,
 SubField(Quotes,'||') AS MoreRows  
INLINE [
  ID,Quotes
  12AD5,MP: 100.87 || BD: 99 || SM: 101.03
];

[petter]

If you rather want to do it directly in a straight table chart you can do it by creating a calculated dimension with the expression =ValueLoop(1,3) and two expressions:

The second expression would change the last parameter to 2.

In the Presentation tab you can select the second dimension and hide it so you wont get a column with the numbers 1,2 and 3.

BTW:

You don't need to know the exact number of subfields - you could for instance say ValueLoop(1,20) to have an upper limit and it will produce just the number of rows that each record from the source has as delimited values.

[m_perreault]

Great thank you!