topic Re: subfield() creates extra rows in QlikView

subfield() creates extra rows

sifatnabil — Tue, 08 Sep 2015 02:51:15 GMT

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.

Re: subfield() creates extra rows

Anonymous — Tue, 08 Sep 2015 03:22:39 GMT

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

As subfield do not duplicates the data

Re: subfield() creates extra rows

sifatnabil — Tue, 08 Sep 2015 03:31:59 GMT

Hi Nitin,

The original data is like this:

Amount, Products,

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.

Re: subfield() creates extra rows

sunny_talwar — Tue, 08 Sep 2015 03:41:21 GMT

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)

Re: subfield() creates extra rows

sasiparupudi1 — Tue, 08 Sep 2015 04:13:43 GMT

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

Hth

Sasi

Re: subfield() creates extra rows

sifatnabil — Tue, 08 Sep 2015 07:55:22 GMT

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

Re: subfield() creates extra rows

sunny_talwar — Tue, 08 Sep 2015 07:57:21 GMT

Ya it does and the expression is also simpler

I am glad I was able to help.

Best,

Sunny