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

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

jcampbell474 — Thu, 16 Apr 2015 13:15:12 GMT

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!

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

sunny_talwar — Thu, 16 Apr 2015 13:19:17 GMT

Try this may be for your 1st issue:

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

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

PrashantSangle — Thu, 16 Apr 2015 13:20:43 GMT

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

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

Thu, 16 Apr 2015 13:25:54 GMT

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

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

sunny_talwar — Thu, 16 Apr 2015 13:29:38 GMT

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

Best,

Sunny

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

jcampbell474 — Thu, 16 Apr 2015 13:29:53 GMT

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!

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

jcampbell474 — Thu, 16 Apr 2015 13:32:28 GMT

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

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

sunny_talwar — Thu, 16 Apr 2015 13:35:18 GMT

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

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

jcampbell474 — Thu, 16 Apr 2015 13:51:34 GMT

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

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

jcampbell474 — Thu, 16 Apr 2015 14:02:26 GMT

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!

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

sunny_talwar — Thu, 16 Apr 2015 14:06:10 GMT

Awesome! I am glad you were able to figure it out.

Best,

Sunny