Need Help

[suryakant241187]

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..!!

[agomes1971]

Hi,

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

Hope it helps.

André Gomes

[Anonymous]

Hi

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

[anbu1984]

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;

[suryakant241187]

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.

[Anonymous]

Hi,

How about below script,

LOAD

     [Activity Code], 

     [Activity Name],

     Num#(LocalEmpNumber) as LocalEmpNumber,

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

<TableName>

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

Thanks

[#7]

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

[anbu1984]

Check this app

[sujeetsingh]

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

    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;