3 Replies Latest reply: May 15, 2016 2:06 PM by Oleg Troyansky RSS

    Set analysis: not equals to, compare 2 time

    Shawn Ng
      rowtime_atime_b
      11/1/2016 20:00:005/1/2016 18:00:00
      22/2/2016 08:00:002/2/2016 14:00:00

       

      This is structure of the table I am working with.

       

      I want to count the total number of times where time_a = time_b by D/M/YYYY

       

      Query in SQL:

      select * from table where
      date(time_a) <> date(time_b);
      
      

       

      What I write in set analysis:

      sum(if(date(time_a)<>date(time_b),1,0))
      
      

       

      What is the correct way to write the condition in set analysis? Thanks!

        • Re: Set analysis: not equals to, compare 2 time
          Stefan Wühl

          If I understood correctly, you want to ignore the time part?

           

          First, take care that your records are correctly interpreted as timestamps:

          Get the Dates Right

          Why don’t my dates work?

           

          e.g. by setting default timestamp format in the script:

           

          Set TimeStampFormat = 'D/M/YYYY hh:mm:ss'; // or 'M/D/YYYY hh:mm:ss' ?

           

          Then your expression to only compare the date parts can look like


          =sum(if( Floor(time_a)<> Floor(time_b),1,0)) 


          You can also create fields in the load script to transform your timestamps to dates, like


          LOAD time_a as datetime_a,

                    DayName(time_a) as date_a,

                    Time(frac(time_a)) as time_a,

                   ....


          Then use date_a / date_b in your expression.


          Set Analysis won't really help here if you need to do a comparison on record level (per primary key).


          Hope this helps,

          Stefan

          • Re: Set analysis: not equals to, compare 2 time
            jagan mohan rao appala

            Hi,

             

            Try like this in script

             

            TableName:

            LOAD

            *,

            If(Floor(TimeStamp#(time_a, 'M/D/YYYY hh:mm:ss')) <> Floor(TimeStamp#(time_a, 'M/D/YYYY hh:mm:ss')), 1, 0) AS DateNotEqualFlag

            select * from table where 

            date(time_a) <> date(time_b);

             

            Now in chart object you can directly use Sum(DateNotEqualFlag) as an expression.

             

            Regards,

            Jagan.

              • Re: Set analysis: not equals to, compare 2 time
                Oleg Troyansky

                Hi,

                 

                let me second Jagan's suggestion to calculate the desired logic in the script. I'd probably add two Date fields and store the Date representations of the two dates there.

                 

                However, to answer your original question "what is the correct Set Analysis syntax for this condition", - the accurate syntax is as follows. You should formulate an Advanced Search condition based on the field that you'd like to apply this selection to. It sounds like the Row field might be the best in your case.

                 

                Judging by the look for your timestamps, they are probably strings (again, it would be better if you converted them to proper timestamp fields in the script). For strings, the Set Analysis condition would look like this:

                 

                COUNT( {<Raw={"=Floor(TimeStamp#(time_a, 'M/D/YYYY hh:mm:ss')) <> Floor(TimeStamp#(time_b, 'M/D/YYYY hh:mm:ss')"}>}  Raw)

                 

                If you'd like to learn more about the use of Advanced Set Analysis and about other advanced Qlik techniques, I recommend to consider joining one of the upcoming sessions of Masters Summit for Qlik - coming soon to Austin, TX and Johannesburg, South Africa. You can also learn advanced QlikView techniques from my book QlikView Your Business - An Expert guide to QlikView and Qlik Sense.

                 

                cheers,

                Oleg Troyansky