count for max date and group by status

[gvasti]

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

[tresesco]

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?

[its_anandrjs]

Use Firstsortedvalue and like this

FirstSortedValue(LOG_DATE,DOMANDA_ID) as FirstSor

Hope this helps

Thanks & Regards

[gvasti]

Hers's in attachment the sample qvw

[its_anandrjs]

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

[its_anandrjs]

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

[maxgro]

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

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

RESULT

SCRIPT

S:

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);

T:

NoConcatenate

load

  *,

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

Resident S

order by DOMANDA_ID, LOG_DATE desc;

DROP Table S;

[Gysbert_Wassenaar]

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.

[gvasti]

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

[gvasti]

Hello Massimo,

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