topic SQL -- help ... is there another way? in QlikView

SQL -- help ... is there another way?

Anonymous — Tue, 14 Aug 2012 13:12:51 GMT

I have some tables loaded in my script, and I want to 'pre-calc' some data between two tables ... somehow I think the way I implemented this is not really efficient . . . is there another way to do such thing?

CALLS_temp:
LOAD Calls.Callnr
         ,Calls.Serviceordernr
RESIDENT Calls;
LEFT JOIN (CALLS_temp)
LOAD Serviceorders.Serviceordernr as Calls.Serviceordernr
         ,Serviceorders.Serviceordernr
RESIDENT Serviceorders;
LEFT JOIN (Calls)
LOAD Calls.Callnr
       ,if(isnull([Calls.Serviceordernr]),'No SO', if(isnull(Serviceorders.Serviceordernr),'yes','no')) as [Calls.SOisRemoved]
RESIDENT CALLS_temp;
DROP TABLE CALLS_temp;

some more explanation on the example above:

I have Calls and I have Serviceorders . . . Calls contain a serviceordernr ... but some serviceordernr's are nog available in de ServiceOrder table (removed).

Someone has suggestions how to do this more efficient??

thx for you help!

Anita

Re: SQL -- help ... is there another way?

hic — Tue, 14 Aug 2012 13:42:54 GMT

// ==========

ServiceOrderNumbers:

Load Serviceorders.Serviceordernr RESIDENT Serviceorders;

Calls:

LOAD *,

if(IsNull([Calls.Serviceordernr]),'No SO',

if(Exists([Serviceorders.Serviceordernr],[Calls.Serviceordernr]),'no','yes')) as [Calls.SOisRemoved]

RESIDENT Calls;

Drop Table ServiceOrderNumbers;

// ==============

This way you can directly check if there is a match between the Serviceordernr in the two tables. No temp tables. No joins.

HIC

Re: SQL -- help ... is there another way?

Anonymous — Tue, 14 Aug 2012 13:46:12 GMT

Thx for your answer . . . somehow I didn't came up with this, although I used it in the past 😉

really thx . . .