Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hello,
I had two problems:
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.
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?
Use Firstsortedvalue and like this
FirstSortedValue(LOG_DATE,DOMANDA_ID) as FirstSor
Hope this helps
Thanks & Regards
Hers's in attachment the sample qvw
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
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
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;
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.
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
Hello Massimo,
thank you for solution, but do not work on my real qvw.. (example domanda_key 203/92)