topic Re: Writing a set analysis issue in QlikView

Writing a set analysis issue

Tue, 12 Aug 2014 14:17:52 GMT

I only want to count the test_id's when the interventiondate is later in time then the datetime.

The expression below I used doesn't work:

count(distinct {<interventiondate={">datetime"} >} test_id)

How do I write this?

Kind regards,

Katleen

Re: Writing a set analysis issue

manideep78 — Tue, 12 Aug 2014 14:19:54 GMT

If you are fine with date format use this: count({<interventiondate={">datetime"} >} distinct test_id)

else convert the datetime to the format you use and try this:

count({<interventiondate={">$(=Date(datetime))"} >} distinct test_id)

Re: Writing a set analysis issue

Wed, 13 Aug 2014 06:58:20 GMT

Apparently I also need to count when the dates are the same.

I tried to adapt it like this, but it doesn't work:

count({<interventiondate={">=$(=Date(datetime))"} >} distinct test_id)

What did I do wrong this time?

Re: Writing a set analysis issue

er_mohit — Wed, 13 Aug 2014 07:00:40 GMT

Make sure date format of both is same

Re: Writing a set analysis issue

Wed, 13 Aug 2014 07:21:57 GMT

interventiondate is a datefield.

datetime is a timestamp, but that should be solved with the date(datetime) function, no?

Re: Writing a set analysis issue

simenkg — Wed, 13 Aug 2014 07:27:01 GMT

use count({<interventiondate={">=$(=Date#(datetime,'DD.MM.YY hh:mm:ss'))"} >} distinct test_id)

replace 'DD.MM.YY hh:mm:ss' with the format you use for the timestamp.

Re: Writing a set analysis issue

simenkg — Wed, 13 Aug 2014 07:28:50 GMT

However if you want this to work as FOR each line: if interventiondate >= datetime count(distinct test_id) .

Then you have to use

sum(aggr(count({<interventiondate={">=$(=Date#(datetime,'DD.MM.YY hh:mm:ss'))"} >} distinct test_id),test_id))

Re: Re: Writing a set analysis issue

Wed, 13 Aug 2014 08:44:57 GMT

Still doesn't seem to work, I have attached a qvw witch some sample data.

Can you take a look at it?

Kind regards,

Katleen

Re: Re: Writing a set analysis issue

er_mohit — Wed, 13 Aug 2014 09:17:26 GMT

See the attached file

Re: Writing a set analysis issue

simenkg — Wed, 13 Aug 2014 09:20:25 GMT

In your load script change it into the following:

Actions:

load *,

if(interventiondate>=datetime,1,0) as IntDateBiggerThanDateTimeFlag;

LOAD _id as id,

action_category,

actionname,

status,

interventiondate,

process_datetime as datetime

FROM

[Apps\2. Extraction to QVD layer\QVD_Ettenleur\Actions_69.qvd]

(qvd);

Then your expression becomes:
count({$<IntDateBiggerThanDateTimeFlag={1}>} distinct id)

Re: Re: Writing a set analysis issue

Wed, 13 Aug 2014 09:41:31 GMT

Works in the example file, but when I insert it into my original script I get the error that the field 'interventiondate' can't be found.

Acties_totaal:

load *,

if(interventiondate>=process_datetime,1,0) as IntDateBiggerThanDateTimeFlag;

LOAD _id as test_id,

_address_id as addresses_id,

action_category,

actionname,

status as acties_totaal_status,

interventiondate as acties_totaal_interventiondate,

process_datetime as acties_totaal_datetime

FROM

[..\2. Extraction to QVD layer\QVD_Ettenleur\Actions_69.qvd]

(qvd);

Can you see what is wrong with my script?

Re: Re: Writing a set analysis issue

simenkg — Wed, 13 Aug 2014 09:46:36 GMT

Yes. This is because we are essentially doing TWO loads. The top one is a load FROM the bottom one. So since you change the name of the field interventiondate, it no longer exists in the top load.

change to:

load *,

if(acties_totaal_interventiondate>=acties_totaal_datetime,1,0) as IntDateBiggerThanDateTimeFlag;

LOAD _id as test_id,

_address_id as addresses_id,

action_category,

actionname,

status as acties_totaal_status,

interventiondate as acties_totaal_interventiondate,

process_datetime as acties_totaal_datetime

FROM

[..\2. Extraction to QVD layer\QVD_Ettenleur\Actions_69.qvd]

(qvd);