5 Replies Latest reply: Oct 2, 2012 8:14 AM by Henric Cronström RSS

    Total count is wrong

      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
          Henric Cronström

          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

              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
                  Henric Cronström

                  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

                      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
                          Henric Cronström

                          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