subfield() creates extra rows

[sifatnabil]

Hi,

Using subfield in script creates duplicate rows with the other delimited values, and when using sum(Amount) in a textbox, the summation is incorrect. How can this be rectified? e.g. if the original number of rows in script is 3, using subfield will double the number of rows to 6 if there are 2 subfields (like Product1, Product2). So sum(Amount) is doubled. Thanks.

[Anonymous]

Can you post the sample of your data for better understanding.

As subfield do not duplicates the data

[sifatnabil]

Hi Nitin,

The original data is like this:

Amount, Products,

10, A, B

10, A, B

10, A, B

After using subfield():

Amount, Products,

10, A,

10, B

10, A,

10, B,

10, A,

10, B

Causing sum(Amount) to be incorrect.

[sunny_talwar · Accepted solution]

Two options:

1) May be use a aggregate function in the text box object to address this issue:

Script:

Table:

LOAD Dim,

  SubField(Product, '|') as Product,

  Value

Inline [

Dim, Product, Value

A, Product 1|Product 2, 50

B, Product 1|Product 3, 90

C, Product 2|Product 3, 120

];

Output in Table Box object:

Expression in Text Box object: =Sum(Aggr(Avg(Value), Dim))

2) Use a Separate table for SubField

Script:

Table:

LOAD *

Inline [

Dim, Product, Value

A, Product 1|Product 2, 50

B, Product 1|Product 3, 90

C, Product 2|Product 3, 120

];

Table2:

LOAD Dim,

  SubField(Product, '|') as Product1

Resident Table;

Expression in Text Box Object: =Sum(Value)

[sasiparupudi1]

Use a preceeding load with distinct to eliminate the duplicates after your load with the subfield

Hth

Sasi

[sifatnabil]

Thanks sunindia, the 2nd option works well. It preserves the original row count.

[sunny_talwar]

Ya it does and the expression is also simpler

I am glad I was able to help.

Best,

Sunny