topic Re: set analysis urgent in QlikView

set analysis urgent

Thu, 05 Dec 2013 22:40:32 GMT

Hello:

I am new to QLikView and I have a CSV document that I am loading into QV but I am running into difficulties when trying to get a specific aggregation.

the table form is like this:

2013-12-04 00:02:35 | 2013 | 12 | 04 | 0 | 5 | ONE | SUCCESS | 57

2013-12-04 00:02:35 | 2013 | 12 | 04 | 0 | 5 | TWO | SUCCESS | 68

2013-12-04 00:02:35 | 2013 | 12 | 04 | 0 | 5 | ONE | FAIL | 23

2013-12-04 00:02:35 | 2013 | 12 | 04 | 0 | 5 | TWO | FAIL | 12

2013-12-04 00:03:55 | 2013 | 12 | 04 | 0 | 5 | ONE | SUCCESS | 69

2013-12-04 00:03:55 | 2013 | 12 | 04 | 0 | 5 | TWO | SUCCESS | 79

2013-12-04 00:03:55 | 2013 | 12 | 04 | 0 | 5 | ONE | FAIL | 34

2013-12-04 00:03:55 | 2013 | 12 | 04 | 0 | 5 | TWO | FAIL | 24

What I am looking for is an expression to get the sum of the last TIMESTAMP for each 5_MIN_INT:

ie the sum of all rows with ATTRIBUTE=FAIL and TIMESTAMP=2013-12-04 00:03:55 which is the Timestamp(Max(TIMESTAMP))

5_MIN_INT | FAILED_TRANS

5 | 58

I can get the whole sum with: sum({<ATTRIBUTE={FAIL}>}DATA) but then it also sums the other TIMESTAMP

I have read at least twice the whole help file for set analysis and I think I should get the correct answer but I have spent two days trying without success.

I have tried the following expressions (and other ones like these) but I Just can't get the desired result, these are the ones that actually make some result that seems to point in the right direction.

=sum({$<ATRIBUTO={FAIL},TIMESTAMP-=$(Min(TIMESTAMP))>}DATA)

=Sum({$<ATRIBUTO={FAIL},TIMESTAMP+={$(=FirstSortedValue(TIMESTAMP,-5_MIN_INT))}>}DATA)

=sum({<ATRIBUTO={FAIL},TIMESTAMP={P(TIMESTAMP=$(Max(TIMESTAMP))}>}DATA)

=sum({<ATRIBUTO={FAIL},TIMESTAMP={"=Max(Aggr(Max(TIMESTAMP),5_MIN_INT))"}>}DATA)

The last one actually DOES bring me the correct result but for the WRONG timestamp, ie not the last timestamp but the first one.

BTW 5_MIN_INT is really a drill-down dimension going from YEAR->MONTH->DAY->HOUR->5_MIN_INT

Re: set analysis urgent

wallinpeter — Thu, 05 Dec 2013 23:22:50 GMT

Are you sure your timestamp is really a timestamp and not a text string.

You can tell by trying to format as a number in a tablebox object.

If timestamp is string. Try this....

sum({<ATTRIBUTE={'FAIL'}, TIMESTAMP={$(=maxstring(TIMESTAMP))}>}DATA)

If timestamp is number. Try this...

sum({<ATTRIBUTE={'FAIL'}, TIMESTAMP={$(=max(TIMESTAMP))}>}DATA)

Re: set analysis urgent

Thu, 05 Dec 2013 23:42:47 GMT

I am sure it is a numeric timestamp, I checked with a tablebox, I just tried putting another expression with:

Max(TIMESTAMP) and it returns me a number which corresponds to the timestamp.

Year(Max(TIMESTAMP)) does return me 2013.

I tried both suggestions... but none worked, in both cases I get NULL values (not zeroes).

Re: set analysis urgent

jagan — Fri, 06 Dec 2013 02:34:04 GMT

Hi,

Please find attached file for solution.

Regards,

Jagan.

Re: set analysis urgent

Fri, 06 Dec 2013 14:21:29 GMT

I think you skipped attaching the file

I would attach my qvw but it is in Personal Edition and can only be opened on my computer

Re: set analysis urgent

Anonymous — Fri, 06 Dec 2013 14:29:11 GMT

David, a qvw created in Personal Edition can be opened by any non-Personal. Not the other way around.

Re: set analysis urgent

Fri, 06 Dec 2013 15:03:20 GMT

There goes the qvw.

The field names are in spanish but it is very straight forward.

Re: set analysis urgent

Fri, 06 Dec 2013 15:51:26 GMT

Thanks Jagan Mohan but it stops working with bigger data sets, it gives me just one value for the highest value in the actual set, not one value for each 5_MIN_RANGE.

You can see the qvw I uploaded in this discussion.