topic Re: Re: Re: Re: Exclude values from excel spreadsheet when loading sql data in QlikView

Exclude values from excel spreadsheet when loading sql data

Fri, 07 Nov 2014 10:20:06 GMT

Hello

I am bit of a novice on Qlikview and can't seem to find a way of effectively excluding some data.

So the scenario is I have some data in an excel spreadsheet and I have master data in a sql table, I need to exclude the values that are in the spreadsheet when loading the sql data.

I have so far

Leads:

Select

BusinessName

FROM CampaignManagement..[Leads] L

where enddate is null;

Load

BusinessName,

address,

post_code,

phone,

email,

url

FROM

(biff, embedded labels, table is Adverisers$);

Where the business name in the excel file matches a business name in the Leads table, I need that row excluded.

Any help would be appreciated

Re: Exclude values from excel spreadsheet when loading sql data

Anonymous — Fri, 07 Nov 2014 10:24:15 GMT

Hi,

Try adding this :

...

FROM

(biff, embedded labels, table is Adverisers$)

where not(exists(BusinessName));

Marc.

Re: Exclude values from excel spreadsheet when loading sql data

alexandros17 — Fri, 07 Nov 2014 10:24:41 GMT

try with

Leads:

Select

BusinessName

FROM CampaignManagement..[Leads] L

where enddate is null;

Load

BusinessName,

address,

post_code,

phone,

email,

url

FROM

(biff, embedded labels, table is Adverisers$)

where not exists(BusinessName, BusinessName);

let me know

Re: Exclude values from excel spreadsheet when loading sql data

Fri, 07 Nov 2014 10:34:25 GMT

Neither of the above is excluding the records

Re: Exclude values from excel spreadsheet when loading sql data

Anonymous — Fri, 07 Nov 2014 10:40:48 GMT

My previous solution may work if you have not loaded your BusinessName before. So I assume you need another way, let's try with:

Leads:

Select

BusinessName

FROM CampaignManagement..[Leads] L

where enddate is null;

Table1:

Load

BusinessName,

address,

post_code,

phone,

email,

url

FROM

(biff, embedded labels, table is Adverisers$);

left join (table1) load *, 1 as excludename resident Leads;

Table2: noconcatenate load * resident Table1 where excludename<>1;

drop table Table1;

Marc.

Re: Exclude values from excel spreadsheet when loading sql data

Fri, 07 Nov 2014 10:48:29 GMT

I get an error

Table not found

left join (table1) load *, 1 as excludename resident Leads

Field not found - <excludename>

Table2: noconcatenate load * resident Table1 where excludename<>1

Re: Exclude values from excel spreadsheet when loading sql data

Anonymous — Fri, 07 Nov 2014 10:51:46 GMT

Please name your table. See where I typed "Table1" before load statement.

Marc.

Re: Exclude values from excel spreadsheet when loading sql data

Fri, 07 Nov 2014 10:55:15 GMT

I think I have ??

Leads:

Select

BusinessName

FROM CampaignManagement..[Leads] L

where enddate is null;

Table1:

Load

BusinessName,

address,

post_code,

phone,

email,

url

FROM

(biff, embedded labels, table is Adverisers$);

left join (table1) load *, 1 as excludename resident Leads;

Table2: noconcatenate load * resident Table1 where excludename<>1;

drop table Table1;

Re: Exclude values from excel spreadsheet when loading sql data

Anonymous — Fri, 07 Nov 2014 10:58:23 GMT

ok, then force the no-concatenation. Please add this before load statement:

Table1: Noconcatenate

Marc.

Re: Exclude values from excel spreadsheet when loading sql data

Fri, 07 Nov 2014 11:02:52 GMT

It was

left join (table1) load *, 1 as excludename resident Leads;

should have been

left join (Table1) load *, 1 as excludename resident Leads;

ANYWAY...still not excluding the data

Sooo strange

Re: Exclude values from excel spreadsheet when loading sql data

Anonymous — Fri, 07 Nov 2014 11:05:50 GMT

You are right, sorry...

Can you share a sample file, please?

Marc.

Re: Re: Exclude values from excel spreadsheet when loading sql data

Fri, 07 Nov 2014 11:08:15 GMT

Thats ok

I have attached a sample xls

the database table is just a simple table that holds businessname, address etc

Re: Re: Re: Exclude values from excel spreadsheet when loading sql data

Anonymous — Fri, 07 Nov 2014 11:16:15 GMT

Please Find Attached.

Marc.

Re: Re: Re: Exclude values from excel spreadsheet when loading sql data

Fri, 07 Nov 2014 11:23:03 GMT

Ok...I see

But if I add the BusinessName Column to the Tablebox, it adds in Ark Dental Practise...I need this to not show up at all

Re: Re: Re: Exclude values from excel spreadsheet when loading sql data

Anonymous — Fri, 07 Nov 2014 11:25:49 GMT

In case you do not need excluded vales, then drop Leads table in script:

Drop table Leads;

Marc.

Re: Re: Re: Exclude values from excel spreadsheet when loading sql data

Fri, 07 Nov 2014 11:29:57 GMT

Errmm... No i need Leads table

I need all the values in Leads except the ones that are in the excel...does that make sense?

Re: Re: Re: Re: Exclude values from excel spreadsheet when loading sql data

Anonymous — Fri, 07 Nov 2014 11:37:01 GMT

Well, depending on the requirements it will make sense or not. Check attached again and compare table object and table chart object.

I hope it clarifies your doubts.

Marc.

Re: Re: Re: Re: Exclude values from excel spreadsheet when loading sql data

Fri, 07 Nov 2014 12:04:14 GMT

Ok the chart object makes sense...but when i do it with my full data it tells Allocated Memory exceeded.

Not going well for me

Re: Re: Re: Re: Exclude values from excel spreadsheet when loading sql data

Anonymous — Fri, 07 Nov 2014 12:10:36 GMT

Vaishali,

I understand your initial requirement was accomplished. Please close this thread and open a new one to solve your Memory error.

Marc.