topic Re: Doubts about repeated rows in QlikView

Doubts about repeated rows

Mon, 27 Oct 2014 19:03:07 GMT

Hi everybody. I have repeated values in a table, and i have problems showing them. My english leaves a lot to be desired, i´ll put an example.

Field 1 = 2070

Field 2 = 1

Field 3 = sum(field 1 - field 2)= 6207

Ok.. i guess the row is repeated 3 times and i have 2070 x 3 and 1 x 3 but i can´t see the values and if the user sees this it seems like an error. What i have to do to see the field 1 as 6210 and the field 2 as 3 ??

Thank you all

Greetings.

Re: Doubts about repeated rows

JonnyPoole — Mon, 27 Oct 2014 19:11:58 GMT

In your expression formula, are you using 'sum(Field1)' ? Or just 'Field1' ?

Re: Doubts about repeated rows

its_anandrjs — Mon, 27 Oct 2014 19:12:19 GMT

I suggest use Distinct for this this will take only 2070 and 1 then write like

Field 3 = Sum(Distinct field 1 ) - Sum( Distinct field 2 )

Regard

Anand

Re: Doubts about repeated rows

Mon, 27 Oct 2014 19:15:59 GMT

Í´ve just edited my post.

The expressions are:

Field 1 = 2070

Field 2 = 1

Field 3 = sum(field 1 - field 2)= 6207

Sory for this.

Is clear now? The F1 and F2 dont have sum. I tried but it doesn´t works.

Re: Doubts about repeated rows

Mon, 27 Oct 2014 19:16:56 GMT

Í´ve just edited my post.

The expressions are:

Field 1 = 2070

Field 2 = 1

Field 3 = sum(field 1 - field 2)= 6207

Sory for this.

Can you read again? Sory!

Re: Doubts about repeated rows

its_anandrjs — Mon, 27 Oct 2014 19:26:22 GMT

Did you try like

Field 3 = Sum(Distinct field 1 ) - Sum( Distinct field 2 )

Re: Doubts about repeated rows

Mon, 27 Oct 2014 19:34:39 GMT

yeah, and it´s works .I mean it changes the field 3, but isnt that incorrect? 6207 is the real value, isnt the problem with the values of fields 1 and 2 ?

Regards.

PD: Please tell me if my english isn´t clear and i´ll try to explain it better.

Re: Doubts about repeated rows

JonnyPoole — Mon, 27 Oct 2014 19:44:43 GMT

There a multiple ways to do this calculation depending on what you want to do .

The below example shows 2 sales for the same customer, notice how depending on the expression, you either do the subtraction at the details and sum the results OR you sum the details and subtract the totals.

If you remove the 2nd dimension you can see the results.

So multiple answers depending how you want the calculations to work...

Re: Doubts about repeated rows

Mon, 27 Oct 2014 19:51:22 GMT

Try this -

Field 1 = sum(2070)

Field 2 = sum(1)

Field 3 = sum(field 1 - field 2)= 6207

Since this may be an entirely different issue (data model, naming, etc), if you can attach a sample qvw that would be great.

Re: Doubts about repeated rows

Mon, 27 Oct 2014 19:52:51 GMT

Ok im a bit confused now haha.

Look, my field 3 is ok. I´d like to see the field 1 and 2 in the correct way to see for example 6207 // 3 // 6210.

Thank you

Greetings.

Re: Doubts about repeated rows

JonnyPoole — Mon, 27 Oct 2014 19:57:44 GMT

Field 1: sum(2000)

Field 2: sum(1)

Field 3: sum( 1999) or sum (2000 - 1) or sum (2000) - sum(1) ... your choice

Re: Doubts about repeated rows

Mon, 27 Oct 2014 20:09:30 GMT

Ok the mistake was THE SUM(1 - 2 )

Now i´ve put the fields like

SUM(FIELD1)

SUM(FIELD2)

and SUM(FIELD 1) - SUM(FIELD2)

thank you!!

Re: Doubts about repeated rows

JonnyPoole — Mon, 27 Oct 2014 20:26:55 GMT

Glad it worked . Any followup questions ? Just ask or please close out the thread.