topic Re: need help in QlikView

need help

Thu, 04 Jun 2015 11:21:36 GMT

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

Re: need help

MayilVahanan — Thu, 04 Jun 2015 11:52:44 GMT

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;

Re: need help

sunny_talwar — Thu, 04 Jun 2015 11:57:42 GMT

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;

Re: need help

qlikoqlik — Thu, 04 Jun 2015 12:14:18 GMT

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

Re: need help

IAMDV — Thu, 04 Jun 2015 12:18:21 GMT

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,

Re: need help

qlikoqlik — Thu, 04 Jun 2015 12:59:12 GMT

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

Re: need help

Fri, 05 Jun 2015 13:43:28 GMT

Thanks Deepak

Re: need help

Fri, 05 Jun 2015 13:43:56 GMT

Thank You

Re: need help

Fri, 05 Jun 2015 13:45:31 GMT

Hi Sunindia,

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

Re: need help

sunny_talwar — Fri, 05 Jun 2015 14:39:27 GMT

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

Best,

Sunny

Re: need help

IAMDV — Sat, 06 Jun 2015 10:17:31 GMT

Cheers! Glad it worked. I solved many business problems using FirstSortedValue().