Set analysis: not equals to, compare 2 time

[#1]

row	time_a	time_b
1	1/1/2016 20:00:00	5/1/2016 18:00:00
2	2/2/2016 08:00:00	2/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!

[swuehl]

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

[jagan]

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.

[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