Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
afuchten
Valued Contributor

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

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

Tags (1)
1 Solution

Accepted Solutions
Employee
Employee

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

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

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

2 Replies
Employee
Employee

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

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

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

afuchten
Valued Contributor

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

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

really thx . . .

Community Browser