topic Re: count for max date and group by status in QlikView

count for max date and group by status

gvasti — Wed, 26 Mar 2014 11:28:35 GMT

Hello,

I have a log table with these following fields:

DOMANDA_ID	LOG_DATE	LOG_STATO
1	20/01/2013	SOSPESA
1	22/01/2013	APPROVATA SENZA COPERTURA
1	02/03/2013	APPROVATA
2	06/02/2013	SOSPESA
2	21/02/2013	RESPINTA
3	23/05/2013	APPROVATA
4	28/08/2013	SOSPESA
4	05/12/2013	APPROVATA SENZA COPERTURA
5	15/07/2013	SOSPESA
6	21/09/2013	RESPINTA

DOMANDA_ID is the key and LOG_STATO is a description.

Selecting the year from YEAR(LOG_DATE) field, I need to show the count of DOMANDA_ID based on max LOG_DATE and group by every LOG_STATO. For example, for 2013 there are the following records:

DOMANDA_ID	LOG_DATE	LOG_STATO
1	02/03/2013	APPROVATA
2	21/02/2013	RESPINTA
3	23/05/2013	APPROVATA
4	05/12/2013	APPROVATA SENZA COPERTURA
5	15/07/2013	SOSPESA
6	21/09/2013	RESPINTA

.. and then I need to show this pivot :

LOG_STATO	COUNT(DOMANDA_ID)
APPROVATA	2
APPROVATA SENZA COPERTURA	1
RESPINTA	2
SOSPESA	1

I tried using Max, Aggr, FirstSortedValue and set analysis without find a solution. Have you got a similar case?

Thank you

Giovanni

Re: count for max date and group by status

tresB — Wed, 26 Mar 2014 11:35:13 GMT

You are on right track; those would take you to the solution. Right combination and ordering of them is the key. Could you share your sample qvw?

Re: count for max date and group by status

its_anandrjs — Wed, 26 Mar 2014 11:42:40 GMT

Use Firstsortedvalue and like this

FirstSortedValue(LOG_DATE,DOMANDA_ID) as FirstSor

Hope this helps

Thanks & Regards

Re: Re: count for max date and group by status

gvasti — Wed, 26 Mar 2014 12:09:46 GMT

Hers's in attachment the sample qvw

Re: count for max date and group by status

its_anandrjs — Wed, 26 Mar 2014 12:17:58 GMT

Use Maxstring function to find out max date in place of Firstsorted value like below

MaxString(LOG_DATE) as MaxDate

Let me know about this

Hope this helps

Thanks & Regards

Re: count for max date and group by status

its_anandrjs — Wed, 26 Mar 2014 12:21:07 GMT

Hi,

Check this for your answer please copy and paste this code for report

Temp:

load Date(Date#(LOG_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') as LOG_DATE,DOMANDA_ID,LOG_STATO;

LOAD * Inline

[

DOMANDA_ID, LOG_DATE, LOG_STATO

1, 20/01/2013, SOSPESA

1, 22/01/2013, APPROVATA SENZA COPERTURA

1, 2/3/2013, APPROVATA

2, 6/2/2013, SOSPESA

2, 21/02/2013, RESPINTA

3, 23/05/2013, APPROVATA

4, 28/08/2013, SOSPESA

4, 5/12/2013, APPROVATA SENZA COPERTURA

5, 15/07/2013, SOSPESA

6, 21/09/2013, RESPINTA

];

Right Join

load

DOMANDA_ID,

MaxString(LOG_DATE) as MaxDate,

Count(DOMANDA_ID) as IDCount

Resident Temp

Group By DOMANDA_ID;

NoConcatenate

Newdata:

LOAD DOMANDA_ID,LOG_DATE,LOG_STATO,MaxDate,IDCount

Resident Temp

Where LOG_DATE = MaxDate;

DROP Table Temp;

And take a straight table

Dimension : LOG_STATO

Expression : Count(IDCount)

Hope this helps

Thanks & Regards

Re: count for max date and group by status

maxgro — Wed, 26 Mar 2014 12:33:39 GMT

You can add just a flag to your data and use in expression

count({$<FlagLast={1}>} DOMANDA_ID)

RESULT

SCRIPT

load rowno() as id, *;

LOAD DOMANDA_ID,

LOG_DATE,

LOG_STATO

FROM

[http://community.qlik.com/thread/112060]

(html, codepage is 1252, embedded labels, table is @1);

NoConcatenate

load

if(peek(DOMANDA_ID)<>DOMANDA_ID,1,0) as FlagLast

Resident S

order by DOMANDA_ID, LOG_DATE desc;

DROP Table S;

Re: count for max date and group by status

Gysbert_Wassenaar — Wed, 26 Mar 2014 12:36:13 GMT

There are (at least) two ways to do this. The first is creating a summary table in the script. The second is using a calculated dimension. See attached qvw for both solutions.

Re: Re: count for max date and group by status

gvasti — Wed, 26 Mar 2014 14:17:07 GMT

Hi Anand,

on sample qvw all solutions are ok. I tried to insert your script in my real qvw but results are wrong.

For example, Domanda_Key 203/93 has two LogStatoEvento values, instead could be only LogStateEvento (Respinto) because there is the max LogDataEvento.

You can find in attachment the qvw

Thank you

Re: Re: count for max date and group by status

gvasti — Wed, 26 Mar 2014 14:41:30 GMT

Hello Massimo,

thank you for solution, but do not work on my real qvw.. (example domanda_key 203/92)

Re: count for max date and group by status

gvasti — Thu, 27 Mar 2014 10:58:09 GMT

Hello,

I had two problems:

there were some records without value on LOG_STATO, so the aggr(FirstSortedValue(LOG_STATO,-DATA_LOG) did not work properly
aggr(FirstSortedValue(LOG_STATO,-DATA_LOG) had problems with some values on DATA_LOG, I do not know why...

So I find the solution using ROWNO() AS LOG_ID, WHERE LOG_STATO<>'' and ORDER BY DOMANDA_ID, LOG_DATE in script. Then in pivot i used =aggr(FirstSortedValue(LOG_STATO,-LOG_ID),DOMANDA_ID) as dimension and COUNT(DISTINCT DOMANDA_ID) as expression.

Thank you to all for your response.