topic Re: Creating Records Looping Through Subfields in App Development

Creating Records Looping Through Subfields

m_perreault — Tue, 21 Aug 2018 23:25:12 GMT

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

Re: Creating Records Looping Through Subfields

petter — Tue, 21 Aug 2018 23:53:36 GMT

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
];

Re: Creating Records Looping Through Subfields

petter — Wed, 22 Aug 2018 00:11:07 GMT

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.

Re: Creating Records Looping Through Subfields

m_perreault — Wed, 22 Aug 2018 18:21:57 GMT

Great thank you!