topic Re: Nested problem in QlikView

Nested problem

ariel_klien — Tue, 16 Jul 2013 04:32:51 GMT

Hello all,

I need help with the following:

i have a table like this:

Object_Type	Object_ID	DATE
AAA	1	01/01/13
AAA	2	01/01/13
AAA	3	02/01/13
AAA	2	05/01/13
AAA	1	05/01/13
BBB	1	01/01/13
BBB	2	01/01/13
BBB	3	01/01/13
BBB	4	01/01/13
BBB	4	02/01/13

I need to count only the first Object_Type & Object_ID .

Discard the raw's in bolt.

If I will use count(Object_Id) in expression and Object_Type and Date in Dimensions I will get:

Object_Type	DATE	Count(Object_ID)
AAA	01/01/13	2
AAA	02/01/13	1
AAA	05/01/13	2
BBB	01/01/13	4
BBB	02/01/13	1

I need that the table will be like:

Object_Type	DATE	Count
AAA	01/01/13	2
AAA	02/01/13	1
BBB	01/01/13	4

How can I achieve that?

Ariel

Re: Nested problem

Tue, 16 Jul 2013 05:20:09 GMT

Hi,

Do you have any condition to filter the data in table.

Re: Nested problem

ariel_klien — Tue, 16 Jul 2013 05:31:30 GMT

Hi Kumar

I dont have any conditions.

Ariel

Re: Nested problem

suniljain — Tue, 16 Jul 2013 05:34:27 GMT

Are you want to dispaly maximum date for object type or what ?. I am confused with the final output table you provided .

Re: Nested problem

ariel_klien — Tue, 16 Jul 2013 05:40:56 GMT

Hi Jain,

I want to count only the first value in the table.

For example Object_Type = 'AAA' , Object_ID=1 it appeared 2 times in the table (01/01/13, 05/01/13)

I want to count him only in the date 01/01/13 and discard him in 05/01/13.

Ariel

Re: Nested problem

Anonymous — Tue, 16 Jul 2013 07:02:27 GMT

To count up the number of entries appearing on the first date that a Object_Type occured, something like this might work as an expression, with Object_Type as the dimension:

count(if(DATE=min(DATE),Object_ID)

or using set analsysis:

count({<DATE={'=min(DATE)'}>} Object_ID)

For the date column, you could then use:

only(if(DATE=min(DATE),DATE)

Jonathan

Re: Nested problem

ariel_klien — Tue, 16 Jul 2013 07:49:35 GMT

Hi Jonathan

Thanks for your answer but it dosn't work.

Re: Nested problem

Anonymous — Tue, 16 Jul 2013 08:03:49 GMT

Seems you need to calculate the minimum date across each type. Try:

count(if(DATE=aggr(NODISTINCT min(DATE),Object_Type),Object_ID))

See attached.

Jonathan

Re: Nested problem

ariel_klien — Tue, 16 Jul 2013 08:47:24 GMT

Hi Jonathan

Still not good.

in your QVW the table contain 1 date (01/01/2013) with 2 types (AAA,BBB) but should have 2 dates (01/01/13,02/01/13) - see the table above.

Re: Nested problem

Anonymous — Tue, 16 Jul 2013 10:45:56 GMT

Like Sunil, I'm confused about the decision of what dates to output.

How do you decide to output AAA on 02/01/13 ?

Jonathan

Re: Nested problem

ariel_klien — Tue, 16 Jul 2013 11:42:10 GMT

The Answer..

http://community.qlik.com/thread/87017?tstart=0