topic Re: Total count is wrong in QlikView

Total count is wrong

Tue, 02 Oct 2012 10:17:26 GMT

How come when i run this piece of code

IntervalMatch (MarshallingTimeTaken) LOAD MinutesStart as MSM, MinutesEnd as MEM Resident TimeInMinutes;

and do a Count(MSM),

by right I have 220 rows, but I only got 22 rows

Re: Total count is wrong

hic — Tue, 02 Oct 2012 10:37:12 GMT

It's impossible to say why without seeing the data. But my guess is that you for some reason really only have 22 matches between the intervals in the TimeInMinutes table and the MarshallingTimeTaken, which resides in another table.

HIC

Re: Total count is wrong

Tue, 02 Oct 2012 10:54:20 GMT

Hi Henric, I found out whats the cause of problem. The 22 rows comes from MarshallingTimeTaken.

when i do a count(MSM), it return me count (DISTINCT MarshallingTimeTaken).

Any idea why it happened?

Re: Total count is wrong

hic — Tue, 02 Oct 2012 11:09:10 GMT

When you use intervalmatch, it creates a third table with just MarshallingTimeTaken, MSM and MEM. (Do a ctrl-T, and you'll see.) This means that MarshallingTimeTaken is now a key linking two tables and the function Count() is no longer defined. (In which table should QlikView do the count?) But Count(distinct...) is well defined, so this is what the function returns.

You need to use the count function on a field that is not a key.

Finally, why do you rename MinutesStart as MSM and MinutesEnd as MEM? Don't you want the intervalmatch table to link to your TimeInMinutes table?

HIC

Re: Total count is wrong

Tue, 02 Oct 2012 11:23:40 GMT

Hi, the reason I rename it is because I got intervalmatch for another two table like this

IntervalMatch (MarshallingTimeTaken) LOAD MinutesStart as MSM, MinutesEnd as MEM Resident TimeInMinutes;

IntervalMatch (ConsumptionTimeTaken) LOAD MinutesStart as MSC, MinutesEnd as MEC Resident TimeInMinutes;

IntervalMatch (TotalTimeTaken) LOAD MinutesStart as MST, MinutesEnd as MET Resident TimeInMinutes;

Because I am trying to make a cluster bar chart. Am I doing it wrong? please advice me.

Re: Total count is wrong

hic — Tue, 02 Oct 2012 12:14:47 GMT

I don't understand what the problem is...

You can make a clustered bar chart if you have several dimensions, or if you have one dimension and several expressions. And this is true irrespectivley of which data model you choose to create.

On the data model: Do you want the three pairs MSM/MEM, MSC/MEC and MST/MET to be uncorrelated? If so, you probably are doing the right thing. But at this stage, I do not see any problem in having then correlated, i.e. let them keep their original names: MinutesStart, MinutesEnd,

HIC