topic Not Exists in a table in QlikView

Not Exists in a table

Anonymous — Thu, 12 Feb 2015 08:47:48 GMT

Hi all,

I am fairly new to QV, and still struggling on pulling complicated items,

i have tried to solve this, but got into a point where i need the help of community friends

The Challenge is:

i have a sales table, with ads (I call it JobId),

and the type of posting it had (I call it SaleType),

The SaleType column can show these values:

1. 'Base' - which is basic posting, only publish the ad

2. 'Social Boost' / 'Super Size' / etc - which is an upgraded posting on top of the Basic one

like this:

So, you can see the YELLOW lines indicate that a specific JobId was posted on Oct 2014,

with a 'Base' SaleType AND 3 additional Upgrades.

I would like to pull only the ads that have only 'Base' SaleType on a specific month.

In SQL i would just use a not exists statement, but here i don't know what is the solution,

i expect that a BI tool like QV, will offer not less analytical functions like a standard TSQL offers...

i added my SQL query only to elaborate on this challenge (see below)

SELECT b.AffiliateID, DATEPART(YEAR,b.SaleDate) [Year], DATEPART(Month,b.SaleDate) [month], count(distinct b.jobid)

FROM FactMonthlyConversion b

WHERE 1=1

AND b.SaleType='Base'

AND NOT EXISTS

(

SELECT *

FROM FactMonthlyConversion a

WHERE 1=1

and (a.SaleType like '%Boost' or a.SaleType='super size' or a.SaleType='super size lite')

AND a.JobId=b.JobId

and DATEPART(YEAR,a.SaleDate)=DATEPART(YEAR,b.SaleDate)

and DATEPART(Month,a.SaleDate)=DATEPART(Month,b.SaleDate)

)

GROUP BY b.AffiliateID, DATEPART(YEAR,b.SaleDate) , DATEPART(Month,b.SaleDate)

Re: Not Exists in a table

jagan — Thu, 12 Feb 2015 09:07:57 GMT

Hi,

In Qlikview also we have not exists function, check help file for reference

Table1:

LOAD

FROM Table1;

Table2:

LOAD

ID,

Column1,

Column2,

FROM Table2

WHERE NOT EXISTS(ID);

Hope this helps you.

Regards,

Jagan.

Re: Not Exists in a table

Anonymous — Thu, 12 Feb 2015 12:21:38 GMT

Hi Jagan,

The Exists statement doesn't remove anything,

meaning that Table 2 is not changed after the exists statement...

do you know what is wrong here?

//Table 1

FactMonthlyConversion:

LOAD AffiliateID as AffiliateId,

Year(SaleDate) & '-' & Num(Month(SaleDate),'00') as [Year-Month],

SaleType,

JobId

FROM [1_raw\FactMonthlyConversion.qvd] (qvd);

//This is the basic Table

//Table 2

FactMonthlyConversionNonUpgraded:

LOAD AffiliateId,

[Year-Month],

If(SaleType='Base', JobId,'') as [Ads]

Resident FactMonthlyConversion;

//Table 3

FactMonthlyConversionUpgraded:

LOAD AffiliateId,

[Year-Month],

IF(wildmatch(SaleType,'*Boost', 'Super Size Lite', 'Super Size'), JobId,'') as [Ads]

Resident FactMonthlyConversion

WHERE NOT EXISTS([Ads], [Year-Month]);

exit script;

Re: Not Exists in a table

Anonymous — Thu, 26 Mar 2015 13:48:21 GMT

the think is that i want to add conditions to the NOT Exists,

like ID and Date,

so qlikview needs to have the same column name in order to know how to do the condition of the exists?

Re: Not Exists in a table

richard_chilvers — Thu, 26 Mar 2015 16:36:04 GMT

I'd prefer to keep the QV table exactly as you show in your original question, and then count the number of entries for each JobID and create a table showing only those where the count is equal to 1.

Or perhaps I misunderstand the requirement ?

Re: Not Exists in a table

awhitfield — Thu, 26 Mar 2015 16:50:31 GMT

Hi Nir,

do you actually need to load this bit:

//Table 3

FactMonthlyConversionUpgraded:

LOAD AffiliateId,

[Year-Month],

IF(wildmatch(SaleType,'*Boost', 'Super Size Lite', 'Super Size'), JobId,'') as [Ads]

Resident FactMonthlyConversion

If all you are the 'base' sales type, which appear to be loaded in the previous step?

Andy

Re: Not Exists in a table

Anonymous — Sun, 29 Mar 2015 11:12:14 GMT

thanks for helping,

I have 2 tables:

1. a table with all ID's

2. a table with only ID's of upgraded jobs

i want to do a not exists condition in order to do table(1) - table(2), so i will have only the non upgraded jobs,

but i want to group it by the month,

so:

//The Original table is grouped by months

FactMonthlyConversion:

LOAD AffiliateID as AffiliateId,

Year(SaleDate) & '-' & Num(Month(SaleDate),'00') as [Year-Month],

SaleType,

JobId

FROM [1_raw\FactMonthlyConversion.qvd] (qvd);

//This table will have all ID's

FactMonthlyConversionNonUpgraded:

LOAD AffiliateId,

[Year-Month],

If(SaleType='Base', JobId,'') as [Ads]

Resident FactMonthlyConversion;

//This table will have ID's only of the upgraded ads

FactMonthlyConversionUpgraded:

LOAD AffiliateId,

[Year-Month],

IF(wildmatch(SaleType,'*Boost', 'Super Size Lite', 'Super Size'), JobId,'') as [Ads]

Resident FactMonthlyConversion

WHERE NOT EXISTS([Ads], [Year-Month]);

exit script;

Re: Not Exists in a table