topic Re: Need Help in QlikView

Need Help

suryakant241187 — Mon, 03 Nov 2014 09:32:02 GMT

Hello Team,

I am recieving data in Excel which is conatains more than 10 Lakh of records, I am using some of the columns from that For e.g.

[Activity Code], [Activity Name], Num#(LocalEmpNumber), [Attempt_Completion_Date]

I am getting duplicates record as employee can complete same activity more than one time. and i have to show the activity code with latest compltetion date.

Can anyone please help..!!

Re: Need Help

agomes1971 — Mon, 03 Nov 2014 09:46:20 GMT

Hi,

group by the data and use max( [Attempt_Completion_Date]).

Hope it helps.

André Gomes

Re: Need Help

Anonymous — Mon, 03 Nov 2014 09:52:08 GMT

Use the Code like

Load
[Activity Code],
Max(Date([Attempt_Completion_Date],'DD/MM/YYYY')) as %Date
FROM
C:\Desktop\Test\Data.QVD(qvd) group by [Activity Code];

Load
[Activity Code],
[Activity Name],
Num#(LocalEmpNumber),
[Attempt_Completion_Date] as %Date
FROM
C:\Desktop\Test\Data.QVD(qvd);

Re: Need Help

anbu1984 — Mon, 03 Nov 2014 09:54:47 GMT

Load [Activity Code], Firstsortedvalue([Activity Name],-[Attempt_Completion_Date]), Firstsortedvalue(LocalEmpNumber,-[Attempt_Completion_Date]), FirstSortedValue([Attempt_Completion_Date],-[Attempt_Completion_Date]) Group by [Activity Code];

Load

[Activity Code], [Activity Name], Num#(LocalEmpNumber) As LocalEmpNumber, Date#([Attempt_Completion_Date],'M/D/YYYY') As [Attempt_Completion_Date] From Table;

Re: Need Help

suryakant241187 — Thu, 06 Nov 2014 09:03:43 GMT

Hello,

Not able to find correct reocords. Please see below scenario:

Activity Code	Activity Name	LocalEmpNumber	Attempt Completion Date
ILC-CF	Cards Dev	1234	6/9/2013 0:00
ILC-CF	Cards Dev	1234	6/9/2014 0:00
ILC-CF	Cards Dev	1234	9/27/2011 0:00
ILC-EMG	Planning	1234	9/27/2012 0:00
ILC-EP	E-Payments	1234	5/30/2012 0:00
ILC-FS	Business Analysis	1234	7/2/2013 0:00
ILC-FS	Business Analysis	1234	11/5/2013 0:00
ILC-FS	Business Analysis	1234	11/6/2013 0:00
ILC-EP	E-Payments	1234	12/13/2012 0:00
ILC-HSK	Managing performers	1234	6/16/2011 0:00

As in above :

1234 employee has completed ILC-CF course 3 times but his latest completion date is 6/9/2014 so i requires only this record.

File contains more than lakh records.

can anyone please help.

Re: Need Help

Anonymous — Thu, 06 Nov 2014 09:09:00 GMT

Hi,

How about below script,

LOAD

[Activity Code],

[Activity Name],

Num#(LocalEmpNumber) as LocalEmpNumber,

Date(Max([Attempt_Completion_Date])) as [Attempt_Completion_Date]

GROUP BY [Activity Code], [Activity Name], Num#(LocalEmpNumber);

Thanks

Re: Need Help

Thu, 06 Nov 2014 09:17:40 GMT

Hi Suryakant,

Use max of completion date ,and then use group by other dimensions.

It will take max date for a similar group of records when you use group by with other fields.

thanks

Sudhanshu shrivas

Re: Re: Need Help

anbu1984 — Thu, 06 Nov 2014 09:29:00 GMT

Check this app

Re: Need Help

sujeetsingh — Thu, 06 Nov 2014 09:32:19 GMT

Check this code

Here i have used logic to get the EmpID with only Stat with highest EventRank.

This for script level well at ui level you can easily implement this with dimension as EmpID and taking max() of Event rank.

Tab:

LOAD * INLINE [

EmpID, EventRank, Stat

1, 2, A

1, 5, D

1, 4, F

1, 6, F

2, 1, S

2, 6, S

2, 8, R

4, 3, D

];

left Join(Tab)

LOAD

EmpID

,max(EventRank) as Latest

Resident Tab group by EmpID;

Data:

LOAD

EmpID

,if(Latest=EventRank,Stat,null()) as Stat

Resident Tab where if(Latest=EventRank,Stat,null())<>null();

DROP Table Tab;