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

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

pgriffiths — Fri, 21 Nov 2014 11:59:18 GMT

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.

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

rubenmarin — Fri, 21 Nov 2014 12:05:27 GMT

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

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

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

pgriffiths — Fri, 21 Nov 2014 12:39:06 GMT

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.

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

anbu1984 — Fri, 21 Nov 2014 12:50:47 GMT

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

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

rubenmarin — Fri, 21 Nov 2014 12:53:52 GMT

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

;

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

pgriffiths — Fri, 21 Nov 2014 14:45:57 GMT

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.

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

pgriffiths — Fri, 21 Nov 2014 14:48:01 GMT

Thank you Ruben,

That has works and orders desc.

This is exactly what I needed.

Thank you so much.