topic Re: Problem with duplicate Records in QlikView

Problem with duplicate Records

Anonymous — Mon, 19 Oct 2015 05:40:38 GMT

Hi,

I have a question, i have a qvd file contains of some fields.

Let's say the fields are

Col1 Col2 Col3 Col4 Qty

A A1 A2 A4 0.5

A A1 A3 A5 0.5

First time i try to load only Col1, Col2, and Qty. I also create the pivot table (Col1 and Col2 as Dimension) and sum(Qty) as Expression.

The total expression show me the value 0.5 (the value must be 0.5+0.5 =1).

Second time i try to load all fields (Col1, Col2, Col3, Col4, Qty). Pivot table with Col1 and Col2 as Dimension and sum(Qty) as Expression. The total show me the value 1 (the correct one).

The problem is, is it possible for me to not load Col3 and Col4 but the total value is still correct (0.5+0.5 = 1) ?

Sorry i cant attach my qlikview file here.

Really appreciate your help. Thanks

Re: Problem with duplicate Row

jonathandienst — Mon, 19 Oct 2015 05:50:01 GMT

Can't replicate that. This load calculates the Sum(Qty) correctly regardless of the number of dimensions.

LOAD Col1

,Col2

// ,Col3

// ,Col4

,Qty

Inline

[

Col1, Col2, Col3, Col4, Qty

A, A1, A2, A4, 0.5

A, A1, A3, A5, 0.5

];

Re: Problem with duplicate Records

tresB — Mon, 19 Oct 2015 05:53:30 GMT

Possibly you are getting confused seeing the data in table box. Yes, table box shows unique records. Use aggregation sum(Qty) in straight table and see the correct result as in attached sample.

Re: Problem with duplicate Row

ahmar811 — Mon, 19 Oct 2015 05:59:31 GMT

I don't know how this problem comes because i am try same process but it get right value

here the output come

Re: Problem with duplicate Records

qlikviewwizard — Mon, 19 Oct 2015 06:11:44 GMT

Hi Try like this:

Data:

Load * inline [

Col1,Col2,Col3,Col4,Qty

A,A1,A2, A4, 0.5

A,A1,A3, A5, 0.5

];

Re: Problem with duplicate Records

Anonymous — Mon, 19 Oct 2015 06:20:11 GMT

Hi All,

I think i've found the problem. Sorry for the unclear statement.

In the other side I also join with another data using left join.

here is the load script

Tes:

load

Col1,

Col2,

Qty

Inline [

Col1 Col2 Col3 Col4 Qty

A A1 A2 A4 0.5

A A1 A2 A4 0.5]

(delimiter is '\t');

Left Join(Tes)

LOAD Distinct * INLINE [

Col1, Name, Name2, Name3

A, Name_A, Name2_A, A,

A, Name_A, Name2_A, A

];

I have another data like that structure. And the result give me 0.5

anybody knows why ?

Once again really sorry for the unclear statement.

Thanks

Re: Problem with duplicate Records

qlikviewwizard — Mon, 19 Oct 2015 06:39:47 GMT

Hi Use this script.

Tes:

load

Col1,

Col2,

Qty

Inline [

Col1, Col2, Col3, Col4, Qty

A, A1, A2, A4, 0.5

A ,A1, A2, A4, 0.5];

NoConcatenate

LOAD Distinct * INLINE [

Col1, Name, Name2, Name3

A, Name_A, Name2_A, A,

A, Name_A, Name2_A, A

];

Re: Problem with duplicate Records

Anonymous — Mon, 19 Oct 2015 06:42:35 GMT

Hi Arjun,

Thanks for response.

But i need to "left join" that table and use Name, Name2, and Name3 as dimension on my pivot table

Re: Problem with duplicate Records

qlikviewwizard — Mon, 19 Oct 2015 06:44:00 GMT

What is the issue with this? You can use whatever columns you want.

Re: Problem with duplicate Records

avinashelite — Mon, 19 Oct 2015 06:50:08 GMT

I don't think its the concern of the columns here, what is the exact expression and dimension your using ?