Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Try this may be for your 1st issue:
=Aggr(Max([ASN Scan Date]), [ASN Number]) - Aggr(Min([ASN Scan Date]), [ASN Number])
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
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
Can you share the exact error that pops up when you run the script?
Best,
Sunny
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!
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!!
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
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
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!