How to get latest data only from resident table. Where Not Exists()

[pgriffiths]

I have been trying to work out how to extract only the Latest data from a resident table.

From what I have read the below example should work using Where Not Exists(), but doesn't.

//#####################
//##Load All App Data##
//#####################
Qualify*;
AllAppHistory:
LOAD * Inline 
[AssetName, AppName, Date
0,Outlook,30/09/2014 
1,Outlook,28/10/2014 
2,Outlook,29/10/2014
0,Outlook,26/10/2014];
UNQUALIFY *;
//######################################
//#Create Exists Key from All App Data.#
//######################################
AllAppHistory_Tmp:
Left Join (AllAppHistory)
LOAD *,
 AllAppHistory.AssetName & AllAppHistory.AppName as AllAppHistory.ExistsKey
Resident AllAppHistory;
//#########################################
//#Create table with only Latest App Data.#
//#########################################
QUALIFY *;
LatestApps:
LOAD AllAppHistory.AssetName,
 AllAppHistory.AppName,
 AllAppHistory.Date,
 AllAppHistory.ExistsKey as ExistsKeyTested
Resident AllAppHistory
Where not Exists(LatestApps.ExistsKeyTested, AllAppHistory.ExistsKey)
Order By AllAppHistory.Date desc
;

I was expecting this to output the following table.  

LatestApps.AllAppHistory.AppName	LatestApps.AllAppHistory.AssetName	LatestApps.AllAppHistory.Date	LatestApps.ExistsKeyTested
Outlook	0	30/09/2014	0Outlook
Outlook	1	28/10/2014	1Outlook
Outlook	2	29/10/2014	2Outlook

Instead I got the following table with an extra row that I have highlighted in red.

LatestApps.AllAppHistory.AppName	LatestApps.AllAppHistory.AssetName	LatestApps.AllAppHistory.Date	LatestApps.ExistsKeyTested
Outlook	0	30/09/2014	0Outlook
Outlook	0	26/10/2014	0Outlook
Outlook	1	28/10/2014	1Outlook
Outlook	2	29/10/2014	2Outlook

I can't seem to work out why this is happening, has anyone got any idea?

Many thanks in advance.

[rubenmarin]

Hi, Exists() works for me when I set the "field to search" between simple quotes:

Where not Exists('LatestApps.ExistsKeyTested', AllAppHistory.ExistsKey) 

[pgriffiths]

Hi Ruben,
thanks you for you reply.

I gave this a go, even tried a copy and paste from your example to make sure it was the same, but it does not exclude the row highlighted in red above.

[anbu1984]

AllAppHistory: 

Load *,AssetName & AppName As Key Where Not Exists('Key',AssetName & AppName);

LOAD * Inline  

[AssetName, AppName, Date 

0,Outlook,30/09/2014  

1,Outlook,28/10/2014  

2,Outlook,29/10/2014 

0,Outlook,26/10/2014] ; 

[rubenmarin · Accepted solution]

I made ExistsKeyTested unqualified and it works using field name on exists()

//######################################### 

    QUALIFY *; 

    UNQUALIFY ExistsKeyTested;

    LatestApps: 

    LOAD AllAppHistory.AssetName, 

     AllAppHistory.AppName, 

     AllAppHistory.Date, 

     AllAppHistory.ExistsKey as ExistsKeyTested 

    Resident AllAppHistory 

    Where not Exists(ExistsKeyTested, AllAppHistory.ExistsKey) 

    Order By AllAppHistory.Date desc 

    ; 

[pgriffiths]

Hi Anbu,

Thank you so much for your help.

Your solution was more compact, however did not allow for the order to be sorted so only takes the first record but unsorted.

Many thanks for your time.

[pgriffiths]

Thank you Ruben,

That has works and orders desc.

This is exactly what I needed.

Thank you so much.