11 Replies Latest reply: Dec 31, 2010 8:37 AM by Matt Cayford RSS

    Count query

      Hi guys,
      I hope someone can help.

      I have a seemingly basic query where I wish to count the number of records from our ODBC database where field name pova = 0 , where fields crbrenh,crbrbasic & crbrenh are NULL and where employstatus <> 999001302

      I have tried various different coding but it does not return what I know to be correct. I have put the fields into a table to see what I am looking at and the fields crbrenh,crbrbasic & crbrenh appear to be a mixture of blanks and - so I suspect this is part or all of the problem.


      Any ideas?

       

      Thanks,

       

      Matt

        • Count query

          sum({$<pova={0},crbrenh=,crbrbasic=,employstatus-={999001302}>} 1)

          I didn't tested it but should be something like that.

          Rgds,

          Sébastien

           

            • Count query

              Thanks for your reply Sebastian however this is not working and I have tried different variations on this as well.

              sum({$<pova={0},crbrenh=,crbrbasic=,employstatus-={999001302}>} 1)

               

              As I wish to exclude the value 999001302, I presume you mean the following instead?

              sum({$<pova={0},crbrenh=,crbrbasic=,employstatus<>{999001302}>} 1)

              Also is there any significance in the crbrenh=,crbrbasic=, code as I have previously thought it should be crbrenh=0 or isnull(crbenh) if I want to count blank values?

               

              Thanks,

               

              Matt

                • Count query

                  I'm not sure about the fact to do a sum of 1...

                  The Set analysis explanation is:

                  "crbenh=" : That means take all values for this field. So it will exclude the null values

                  "employstatus -={999001302}" there is a "-" before the "=": That means take everything except 999001302.

                  You should try to replace the Sum( by count if you ha ve an ID field to count on :

                  count(distinct {$<pova={0} .....>} IFFIELD) ...

                  If still not working, test the count by adding each condition one by one...

                  Rgds,

                  Sébastien

                  • Count query
                    Muzammil Syed

                    Hi,

                    Better add some fields at the script level

                    if(len(trim(crbrenh))=0,1,0) as crbrenhFlag,

                    if(len(trim(crbrbasic))=0,1,0) as crbrbasicFlag,

                    And change your expression to---

                    Count({$<pova={0},crbrenhFlag={0},crbrbasicFlag={0},employstatus={'<>999001302'}>} the field you want to count)

                      • Count query

                        Thanks Syed,

                         

                        I have tried pasting your if commands in the Edit Script function after the rest of the existing commands as follows:

                        SQL SELECT empid as personno, crbrbasic, crbrstand, crbrenh, pova, payrollno
                        FROM XXX.dbo.employee where current_ = 1 and payrollno <> ' ';

                        if(len(trim(crbrenh))=0,1,0) as crbrenhFlag;
                        if(len(trim(crbrstand))=0,1,0) as crbrstandFlag;
                        if(len(trim(crbrbasic))=0,1,0) as crbrbasicFlag;

                        but get the following message (which I have only got since I pasted your coding)

                        Error in expression:
                        ')' expected

                        I cannot see where a ')' should fit in this? Should this be elsewhere in the Edit Script command?

                         

                        Thanks,

                         

                        Matt

                          • Count query

                            Your script should be like that:

                            LOAD *,

                            if(isnull(crbrenh),1,0) as crbrenhFlag,

                            if(isnull(crbrstand),1,0) as crbrstandFlag,

                            if(isnull(crbrbasic),1,0) as crbrbasicFlag;

                            SQL SELECT empid as personno, crbrbasic, crbrstand, crbrenh, pova, payrollno
                            FROM XXX.dbo.employee where current_ = 1 and payrollno <> ' ';

                            if(len(trim(crbrenh))=0,1,0) as crbrenhFlag;
                            if(len(trim(crbrstand))=0,1,0) as crbrstandFlag;
                            if(len(trim(crbrbasic))=0,1,0) as crbrbasicFlag;

                            Then try the expression as advised...

                            But the -= works fine, there is no need to do {'<>999xxxxx'}...

                            But if you prefer like that...

                            Rgds,

                            Sébastien

                              • Count query

                                Thanks Spastor but I have tried your suggestion and it is still coming back with the same error Error in expression:
                                ')' expected
                                :

                                LOAD *,

                                (if(isnull(crbrenh),1,0) as crbrenhFlag),

                                (if(isnull(crbrstand),1,0) as crbrstandFlag),

                                (if(isnull(crbrbasic),1,0) as crbrbasicFlag);

                                SQL SELECT empid as personno, crbrbasic, crbrstand, crbrenh, pova, payrollno
                                FROM xxx.dbo.employee where current_ = 1 and payrollno <> ' ';

                                if(len(trim(crbrenh))=0,1,0) as crbrenhFlag;
                                if(len(trim(crbrstand))=0,1,0) as crbrstandFlag;
                                if(len(trim(crbrbasic))=0,1,0) as crbrbasicFlag;

                                I have to say, as a complete beginner, Qlikview can be extremely frustrating (especially when you do not save before Edit Script and if it doesnt work & comes back with an error, you lose all of your changes!

                                  • Count query

                                    I think the goog pne is :

                                    LOAD *,

                                    if(isnull(crbrenh),1,0) as crbrenhFlag,

                                    if(isnull(crbrstand),1,0) as crbrstandFlag,

                                    if(isnull(crbrbasic),1,0) as crbrbasicFlag;

                                    SQL SELECT empid as personno, crbrbasic, crbrstand, crbrenh, pova, payrollno
                                    FROM xxx.dbo.employee where current_ = 1 and payrollno <> ' ';

                                    Don't forget to save before reloading ;)

                                     

                                     

                                    • Count query
                                      Miguel Angel Baeyens de Arce

                                       


                                      cayfmatt wrote:I have to say, as a complete beginner, Qlikview can be extremely frustrating (especially when you do not save before Edit Script and if it doesnt work & comes back with an error, you lose all of your changes!


                                      Agree. Fortunately, there are some good settings to check in QlikView. Go to the Settings menu, User Preferences, and tab "Save". Click on "Save before reload" and set the others as you wish. All these settings are documented in the Reference Manual available in the Dowloads section above as well as when you install QlikView choosing "Complete Install".

                                      Hope this helps!

                                       

                                        • Count query

                                          Thanks Sebastian and Miguel.

                                          Miguel - I have been getting the employstatus from another section of coding within Edit Script but didnt want to paste it all in. I'm now down to about 22 'wrong' records from over 200 before so thanks again, I'll continue playing around with it (also now enabled the auto save function Smile)

                            • Count query
                              Miguel Angel Baeyens de Arce

                              Hello Matt,

                              The script, as suggested by Syed adn Sébastien, should look like

                               

                              Table:LOAD *, // loads all previously fields pulled from the database in the SQL SELECT query If(Len(crbrenh), 0, 1) as crbrenhNullFlag, // If length of field is zero (null or empty or missing) from the database, then store a "1" in this field, otherwise "0" If(Len(crbrstand), 0, 1) as crbrstandNullFlag, If(Len(crbrbasic), 0, 1) as crbrbasicNullFlag; // Missing employstatus below!SQL SELECT empid as personno, crbrbasic, crbrstand, crbrenh, pova, payrollnoFROM xxx.dbo.employee where current_ = 1 and payrollno <> ' ';


                              I always use Len(field) instead of IsNull() because some strange behaviour in different 32bits and 64 bits architectures. Len(Field) will always be equal to zero when there is no value stored in that field in the record.

                              Now create a new text object and type the following

                               

                              Count({< pova = {0}, crbrenhNullFlag = {1}, crbrstandNullFlag = {1}, crbrbasicNullFlag = {1}, employstatus -= {999001302} >} personno)


                              Note that in QlikView field names and values are case sensitive. Note that field "employstatus" used in the expression above is not being loaded by your script nor retrieved from the database in your SQL statement, so you should add it.

                              Hope that helps.