Duplicate values are not displayed in Qlikview

[#1]

Hi,

I have a text box for sales, location and date. And if select a date and lets say that date has multiple same number of sales ( ex multiple locations have 10,000) it only shows up once.I also created a text object which does the sum of sales and even this one also excludes the duplicates.

Can someone help me with any suggestions on how to get around this one?

Thanks

[Anonymous]

text boxes usually show one value. but if you are trying to do a count...you may want to add distinct keyword...soemthing like:

=count(distinct Sales)

however, sum(Sales) should give you a sum total of all the sales on that date unless there is something else that is affecting the selection.

if this doesn't help, you may want to paste your expression, your result and the expected result so we can take a closer look at what is going on.

[#3]

Attached is my QVW i am working on,I am aggregating the sales to the first day of the week and if you see in location 3020 i only see 5 transactions coming out as one is blank and the other one is repeated value however if you change the location to 3021 we can see 7 values and the values add up correctly to what i want to achieve.

Can you throw some light on it?

Thanks

[Anonymous]

i looked at your file but am unable to see what you mean by only 5 transactions coming out for 3020. the sum([Transaction ID] seems to return the correct total, right?

[#5]

Nope, if you see the number of values in transaction count we see 5 values but its supposed to be 6 as 18 is repeated twice and i know that a text box will only show 1 value if its duplicated,however if we see in the text object where the value is shown as 128 it should be 128+18 = 146. Even if i am doing the sum the 18 is getting skipped

[maxgro]

mee too I don't see the repeated 18 in transaction count

[#7]

The data is there - create a table box object to see all the data as is in the table - you have Nulls too.

And do samples of data to verify all is correct to compare with the source. if you are not confident, chuck in an Autonumber field on LOAD so you can check if Qlikview is 'compressing' the data to unique rows. This Autonumber field is temporary as a basic check for 1 to 1 records comparation to your source system. Drop it off to free up memory in your final design.

[#8]

If you create a table box - you will see the data by selections of Loc 3020 and Transaction Count 18 - We see 2 rows only. Sorry I tried to upload my PNG image - it doesnt seem to work for me here.

Also use a Straight Table with Dimension:Loc and Expression:Sum(Transaction Count)   - Im sure its totalling ok.

Then clear selections, Click on the Transaction Count=18 from  Box table  and Loc 3020  - check your sums are correct, ie. 2 dates come up and they sum up .

[#9]

Hi Peter Pierre when you have duplicate values you first step must be:

1.- Desable all catalogs

2.-Run the script and validating the total of sales

3.-enable one bye one catalog and then run again the script and validate de information may you have some records in some catalog in the joins with your tables that have conflict and duplicate the information is very common this problem

for example:

Sales:

LOAD DateId,

LocationNum,

Text(DateId) & Text(LocationNum) & Text(TranNum) As TranId,

TransactionTypeId,

TranCount,

Quantity,

ExtGrossDollars,

ExtDiscountDollars,

ExtNetDollars,

ExtCostDollars,

Margin

FROM ..\..\..\Data\QVD\Extract\_Sales_2014.qvd(qvd);

/ 1.-  *DISABLE ALL CATALOGOS*/

/*

Inner Join (Sales)

LOAD DateId ,

FiscalWeekOfYearId,

FiscalYearId

FROM..\..\..\Data\QVD\Extract\_Date.qvd (qvd);

Inner Join (Sales)

LOAD FiscalYearId,

FiscalWeekOfYearId,

Min(DateId) As DateId

FROM \..\..\Data\QVD\Extract\_Date.qvd(qvd)

Group By  FiscalYearId, FiscalWeekOfYearId;

*/

/*2.-Run the script and validating the total of sales*/

/*3.-enable one bye one catalog and then run again the script and validate de information*/

Sales:

LOAD DateId,

LocationNum,

Text(DateId) & Text(LocationNum) & Text(TranNum) As TranId,

TransactionTypeId,

TranCount,

Quantity,

ExtGrossDollars,

ExtDiscountDollars,

ExtNetDollars,

ExtCostDollars,

Margin

FROM ..\..\..\Data\QVD\Extract\_Sales_2014.qvd(qvd);

/* I ENABLE THE SECOND CATALOG*/

Inner Join (Sales)

LOAD DateId ,

FiscalWeekOfYearId,

FiscalYearId

FROM..\..\..\Data\QVD\Extract\_Date.qvd (qvd);

/*LOOK AT  THIS I DESABLE THIS LAST CATALOG*/

/*

Inner Join (Sales)

LOAD FiscalYearId,

FiscalWeekOfYearId,

Min(DateId) As DateId

FROM \..\..\Data\QVD\Extract\_Date.qvd(qvd)

Group By  FiscalYearId, FiscalWeekOfYearId;

*/

[#10]

Saw your problem in the Load Script.

You are GROUP BY TranCount  so of course you wrill only get 1 instance of TranCount by Loc Num and TransTypeID

So in your source if you have duplicates of TranCount  based on your GroubBy Fields only the first 1 is shown while the rest is vetted out.

You best bet to correct this is to replace

TranCount

As [Transaction Count],

With

Sum(TranCount)

As [Transaction Count],

Group by (NUM#(TEXT(date(weekstart(date#(DateId,'YYYYMMDD'),0,-1),'YYYYMMDD')))), LocationNum,TransactionTypeId,TranCount;