topic Re: Strange Problem in QlikView

Strange Problem

ttal7878 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a strange problem,

When I try to make a sum of the sale_Amount$ column, for no apparent reason the result doubles itself.

It's problematic for me to upload my real model,

and I can't imitate the problem in an example model, So I'll try to explain with picture from my model.

I have a fact table that displays the following data

You can see in the picture that the total Sale Amount$ for the Unique_num is:24$

But, if I do the following calculation:

=sum({<Action=,SaleDateKey={">=$((VMinDate))<=$((VMaxDate))"}>}Sale_Amount$)

I get the result 327$

You can see in the picture that for 'Action' = 'collection', the Sale_Amount$ has doubled.

Can anyone explain, what I'm doing wrong?

Re: Strange Problem

devarasu07 — Tue, 31 Oct 2017 08:31:18 GMT

Hi,

What is the expression inside sum of sales amount $? always try to share your mock source as text/excel instead of pitcure. tks

might date format issue . could you share your mock app? tks

Data:

LOAD *,Timestamp(Timestamp#(SaleDateKey1,'YYYY/MM/DD hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as SaleDateKey INLINE [

Unique_Num, Action, Sale_Amount$, SaleDateKey1

177796860, Collection, 303,2017/10/16 3:17:05

177796860, Sale, 15,2017/10/15 5:59:43

177796860, Lead, 0,2017/10/15 5:56:08

177796860, Refund,-294,2017/10/25 9:02:17

];

Re: Strange Problem

sunny_talwar — Tue, 31 Oct 2017 08:36:24 GMT

I think this might be a data model issue.... it might be duplicating the rows and because you are looking at this in a table box, it only displays the unique combination of your listed dimensions, you are only seeing the values just once

Re: Strange Problem

effinty2112 — Tue, 31 Oct 2017 08:37:34 GMT

Hi Tal Tal,

It's possible that the record for the collection of 177796860 is duplicated. If you add this expression

count({<Action=,SaleDateKey={">=$((VMinDate))<=$((VMaxDate))"}>}Sale_Amount$)

does it return 1 or 2?

Good luck

Andrew

Re: Strange Problem

ttal7878 — Tue, 31 Oct 2017 08:50:46 GMT

How can I know if the model duplicating the rows?

There is a possibility of testing, in order to know that the problem is in the model?

Re: Strange Problem

ttal7878 — Tue, 31 Oct 2017 08:52:47 GMT

It return 2

Re: Strange Problem

effinty2112 — Tue, 31 Oct 2017 08:56:57 GMT

Hi Tal Tal,

I'm afraid then that the record for the collection of this item must be duplicated in your data model so you'll need to go through your load script to look for the error.

good luck

Andrew

Re: Strange Problem

sunny_talwar — Tue, 31 Oct 2017 09:00:56 GMT

May be add the other fields you have in this table to this table box and see if you can find something which might be repeating

Re: Strange Problem

ttal7878 — Tue, 31 Oct 2017 09:05:08 GMT

This is a table with 200 fields,

well I'll try......

Thanks

Re: Strange Problem

ttal7878 — Tue, 31 Oct 2017 09:06:25 GMT

Thank you, at least now I know the source of the problem

Re: Strange Problem

ttal7878 — Tue, 31 Oct 2017 10:00:09 GMT

I have loaded only the fact table,and I get the correct result,

it is possible that the relationships with one of the dimensional tables will create the problem?

Re: Strange Problem

srchagas — Tue, 31 Oct 2017 10:07:59 GMT

Hi yes... Could be

If some one dimension have the same key 2 times for example i will be duplicate the record.

try use one distinct in load of dimensions. one by one in dimensions, u probably will find the dimension with the duplicate record.

or load the dimensions in another Qlikview App, user RowNum() in begin of load and a listbox select the option "Shw Frequence" , one probably will have the number "2"