topic Re: Concatenate WHERE NOT EXISTS() in App Development

Concatenate WHERE NOT EXISTS()

chris1987 — Mon, 31 Oct 2016 15:30:32 GMT

Hi,

I have the following code which works exactly how I want it to, but when I reload the code again, I get the information duplicated. I think I need to put a "WHERE NOT EXISTS" statement in but I can't figure out how to do this.

Here's the code:

TrackerTemp:

LOAD *

FROM

(qvd);

Concatenate(TrackerTemp)

/*Not Made - Due*/

LOAD *,

'ukOpenOrdersNot_MadeDue' & num(today()) & 'UD' as [ID],

'uk' as Database,

'UD' as Entity,

today() as Date,

'OpenOrders' as AppName,

'Not Made' as FieldName,

'Due' as FieldType,

sum(if([Status]='Due' and [Category]='Not Made',[Value To Make],'Not Found')) as [Value]

Resident CustTable;

So I load TrackerTemp, then I get the new information from CustTable and it concatenates the two, but if I get run it again I get duplicate entries.

Thanks

Chris

Re: Concatenate WHERE NOT EXISTS()

Anonymous — Mon, 31 Oct 2016 16:24:15 GMT

Why don't you use Distinct?

Re: Concatenate WHERE NOT EXISTS()

chris1987 — Mon, 31 Oct 2016 16:57:30 GMT

Could you give me an example please? I've never used DISTINCT before, not in QV anyway

Cheers

Chris

Re: Concatenate WHERE NOT EXISTS()

Anonymous — Mon, 31 Oct 2016 17:04:02 GMT

You just need to add distinct after your load statement, I would really recommend you to go through the below link so that you can understand the whole functionality

http://www.qlikfix.com/2013/07/30/distinct-can-be-deceiving/

Re: Concatenate WHERE NOT EXISTS()

chris1987 — Tue, 01 Nov 2016 08:44:29 GMT

Brilliant. Great read. I always thought the LOAD DISTINCT only apply to the information it was loading - not to the resulting table. This worked perfectly.

Cheers

Chris