Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

ariel_klien
Valued Contributor

Nested problem

Hello all,
I need help with the following:
i have a table like this:
Object_TypeObject_IDDATE
AAA101/01/13
AAA201/01/13
AAA302/01/13
AAA205/01/13
AAA105/01/13
BBB101/01/13
BBB201/01/13
BBB301/01/13
BBB401/01/13
BBB402/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_TypeDATECount(Object_ID)
AAA01/01/132
AAA02/01/131
AAA05/01/132
BBB01/01/134
BBB02/01/131
I need that the table will be like:
Object_TypeDATECount
AAA01/01/132
AAA02/01/131
BBB01/01/134
How can I achieve that?
BR
Ariel
Tags (2)
1 Solution

Accepted Solutions
ariel_klien
Valued Contributor

Re: Nested problem

10 Replies
Not applicable

Re: Nested problem

Hi,

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

ariel_klien
Valued Contributor

Re: Nested problem

Hi Kumar

I dont have any conditions.

BR

Ariel

suniljain
Honored Contributor

Re: Nested problem

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

ariel_klien
Valued Contributor

Re: Nested problem

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.

BR

Ariel

jonbrough
Valued Contributor

Re: Nested problem

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

ariel_klien
Valued Contributor

Re: Nested problem

Hi Jonathan

Thanks for your answer but it dosn't work.

jonbrough
Valued Contributor

Re: Nested problem

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

ariel_klien
Valued Contributor

Re: Nested problem

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.


jonbrough
Valued Contributor

Re: Nested problem

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

Community Browser