Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jcampbell474
Creator III
Creator III

Two (Probably Easy) Questions - 1-Set Analysis, 2-Timestamp Variable

Good day, everyone.

I working on an app and encountered two issues that I initially thought would be easy to solve.  Any help would be greatly appreciated.

Issue #1:

I need to get the number of days difference between two dates it equals 0, 1, 2, 3, and >3.  I've tried many different variations of this expression (and others completely different). 

=count({<MaxString([ASN Scan Date])-MinString([ASN Scan Date])=0>} Distinct([ASN Number])) 

Everything I try seems to get hung up on the Max-Min date part. 

Issue #2:

I'm loading hundreds of thousands of records from many tables.  To control the amount of data being retrieved, I use this statement:

and rma.row_create_dt > '12-12-31 23:59:00.000'

which works perfect.  However, there are many tables and I would like to use a variable in place of the date/time.  I use:

Let vstart = '12-12-31 23:59:00.000', then rma.row_create_dt > $(vstart). 

The script always stops, saying it doesn't recognize something.  What is wrong with the syntax?

Thank you!

1 Solution

Accepted Solutions
sunny_talwar

What if you do something like this:

=Count(DISTINCT If(Aggr(Max([ASN Scan Date]), [ASN Number]) -  Aggr(Min([ASN Scan Date]), [ASN Number]) = 0, [ASN Number]))


Sunny

View solution in original post

10 Replies
sunny_talwar

Try this may be for your 1st issue:

=Aggr(Max([ASN Scan Date]), [ASN Number]) -  Aggr(Min([ASN Scan Date]), [ASN Number])

PrashantSangle

Hi,

1: use interval() to find date difference.

try like

count(if(inteval(max([ASN Scan Date])-min([ASN Scan Date]),'D')=ComparingValue,distinct [ASN Number])

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable

Hi Jason,

For Issue#2 try like this by using NUM()


Let vstart =Num( '12-12-31 23:59:00.000');


and in the condition write as

Num(rma.row_create_dt) > $(vstart)


I think this may helpful to you.


Regards

Harsha Nandan

sunny_talwar

Can you share the exact error that pops up when you run the script?

Best,

Sunny

jcampbell474
Creator III
Creator III
Author

This works, but it doesn't count the number of times the difference between dates meets specified criteria.  Ex. # of times = 0, # times = 1, etc...

Thank you!  I didn't think to aggregate.  Certainly helps!

jcampbell474
Creator III
Creator III
Author

This looks very promising, but QV doesn't like the syntax.

I'm using:count(if(interval(max([ASN Scan Date])-min([ASN Scan Date]),'D')='0',distinct [ASN Number])

It doesn't like Distinct and one parenthesis is highlighted.  I couldn't figure out how solve either issue.

Thanks!!

sunny_talwar

What if you do something like this:

=Count(DISTINCT If(Aggr(Max([ASN Scan Date]), [ASN Number]) -  Aggr(Min([ASN Scan Date]), [ASN Number]) = 0, [ASN Number]))


Sunny

jcampbell474
Creator III
Creator III
Author

Thanks!  Looks like I'm headed in the right direction, but QV is saying that Num is not recognized in the load script.

SQL##f - SqlState: 37000, ErrorCode: 195, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]'Num' is not a recognized built-in function name.

SQL Select

rma_rows_id,

row_create_dt

FROM XXX xxx with(NOLOCK)

WHERE rma.rows_id = 1111

and Num(rma.row_create_dt) > 41274.999305556

jcampbell474
Creator III
Creator III
Author

This is it!  I had to add 'Floor' to round down the result being compared to 0 and it works like a charm!

Here is what I ultimately used: =Count(DISTINCT If(Floor(Aggr(Max([ASN Scan Date]), [ASN Number]) -  Aggr(Min([ASN Scan Date]), [ASN Number])) = 0, [ASN Number]))

Many, many thanks!