Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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

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
Highlighted

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

Try this may be for your 1st issue:

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

Highlighted

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

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.
Highlighted
Not applicable

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

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

Highlighted

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

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

Best,

Sunny

Highlighted
Creator III
Creator III

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

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!

Highlighted
Creator III
Creator III

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

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!!

Highlighted

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

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

Highlighted
Creator III
Creator III

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

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

Highlighted
Creator III
Creator III

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

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!