topic LOAD multiple where in QlikView

LOAD multiple where

Thu, 16 Aug 2012 11:09:54 GMT

Hi,

I load a bunch of qvd's, and I want to restrict some data :

Source_table:

LOAD *,

MakeDate(Left(Fiscal_Period,4),Right(Fiscal_Period,3),) as Date

From Report_*.qvd(qvd)

WHERE ([Business segment]='DOWNSTREAM' OR [Business segment]='UPSTREAM'

OR [Business unit] <> 'TRADE' OR [Business unit] <> 'OTHER DOWNSTREAM'

OR [Business unit] <> 'PHOSPHATES' OR [Business unit] <> 'SPECIALITY JVS');

That doesn't really do the trick, because I still see the unwanted data...

are <> wrong tags?

Re: LOAD multiple where

Jason_Michaelides — Thu, 16 Aug 2012 11:19:06 GMT

Looks OK to me. Maybe try it on a single QVD file first? And try removing the brackets around the group of OR statements - I don't think they're needed.

Hope this helps,

Jason

Re: LOAD multiple where

Thu, 16 Aug 2012 11:34:29 GMT

yea Jason I figured it out by myself, it's just a logical thinking issue.

first you need to load what you do want to see with an OR

and with that selection in mind, you need to eliminate the things you don't want to see with AND's.

otherwise you don't get the right results.

This works, but increases loading time heavily.

WHERE ([Business segment]='DOWNSTREAM' OR [Business segment]='UPSTREAM')

AND [Business unit] <> 'TRADE' AND[Business unit] <> 'OTHER DOWNSTREAM'

AND[Business unit] <> 'PHOSPHATES' AND [Business unit] <> 'SPECIALITY JVS');

maybe it would be better to load everything at once, and then make the selections you need from the resident table and drop the source table.

Re: LOAD multiple where

Jason_Michaelides — Thu, 16 Aug 2012 11:52:00 GMT

If it wasn't for the new field you are creating (Date) you could use Exists() instead of WHERE and retain an optimized load. Might be best to do this anyway - you can test what's faster:

1. Load the above using Exists(), with the Date

2. Load the above using Exists(), without the Date, then joining the Date afterwards.

Let me know if you need help with the script from here.

Jason