need help

[#1]

Hi All,

Please help me achieve below requirement in a QV Straight/Pivot table

ID	Number of Meetings Occured	Meetings	Reasons
1	2	7/10/2013	Discussions
		10/11/2013	additional details
2	4	24/6/2014	Missing Key Attendees
		6/8/2014	Not enough details
		26/6/2014	N/A
		25/5/2014	Meeting cancelled

Requirement is to create a table having ID, #Meetings occured, and to get the latest meeting for each ID and the Corresponding Reason for it.

For example

my table should look like below

ID	Number of Meetings Occured	Meetings	Reasons
1	2	10/11/2013	Additional details
2	4	6/8/2014	Not enough details

Please help.

any suggestions appreciated.

Thanks & regards

Jyothi

[MayilVahanan]

Hi

Try like this

Temp:

LOAD If(Len(ID) = 0,Peek('ID'), ID) As ID,

     If(Len([Number of Meetings Occured]) = 0,Peek([Number of Meetings Occured]), [Number of Meetings Occured]) As [Number of Meetings Occured],

     Meetings,

     Reasons

FROM

[https://community.qlik.com/thread/166935]

(html, codepage is 1252, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 8))

));

NoConcatenate

Result:

LOAD ID ,[Number of Meetings Occured], Date(Max(Meetings)) as LatestMeeting, FirstSortedValue(Reasons, -Meetings) as Reasons Resident Temp

Group by ID, [Number of Meetings Occured];

DROP Table Temp;

[sunny_talwar]

Try this:

Table:

LOAD RowNo() as Key,

  ID,

     [Number of Meetings Occured],

     Date#(Meetings, 'DD/MM/YYYY') as Meetings,

     Reasons

FROM

[https://community.qlik.com/thread/166935]

(html, codepage is 1252, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 8))

));

Table1:

NoConcatenate

LOAD If(Len(Trim(ID)) = 0, Alt(Peek('ID'), 0), ID) as ID,

  Meetings,

  Reasons

Resident Table

Order By Key;

Join(Table1)

LOAD ID,

  [Number of Meetings Occured]

Resident Table

Where Len(Trim(ID)) <> 0;

Right Join(Table1)

LOAD ID,

  Max(Meetings) as Meetings

Resident Table1

Group By ID;

DROP Table Table;

[qlikoqlik]

Hi Jyothi

Can you do this in the load script?

If you had to do in the chart:

No of meetings would be straight forward ; count(ID)

Latest Meeting Date : =aggr(max(Meetings),ID)

I am still figuring out how to get the relevant reasons

regards

Padma

[IAMDV · Accepted solution]

Hi Jyothi,

FirstSortedValue() function does what you need and it also supports Set Expression. Check my blog to understand how it works...

http://qlikshare.com/tag/firstsortedvalue/

Cheers,

DV

[qlikoqlik]

Jyothi

what Deepak said works beautifully in a chart

To get the latest date

=FirstSortedValue(Meetings, - Meetings)

To get the relevant Reaosns:

=FirstSortedValue(Reasons,- Meetings)

Thanks

Padma

[#7]

Thanks Deepak

[#8]

Thank You

[#9]

Hi Sunindia,

I was looking for implementing it in chart expression and my mistake i dint mention in the question. Thank you

[sunny_talwar]

No problem my friend. I am glad that it got resolved

Best,

Sunny