Not Exists in a table

[Anonymous]

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)

[jagan]

Hi,

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

Table1:

LOAD

ID

FROM Table1;

Table2:

LOAD

ID,

Column1,

Column2,

'

'

'

FROM Table2

WHERE NOT EXISTS(ID);

Hope this helps you.

Regards,

Jagan.

[Anonymous]

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;

[Anonymous]

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?

[richard_chilvers]

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 ?

[awhitfield]

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

[Anonymous]

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