Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
shaimamahmoud
Contributor

INNER JOIN Doesn't filter Data and brings unwanted data

H i There,

I have this script to view its data in the table box below, but as you see the table views data with null sales2013 and null sales2014 which I don't want to view, how should I hide them  from the table

CustomJOINTable20132014:

LOAD

Manufacturer, [Product Name], Region, Segment, Sum(Sales) as "Sales2013"

Resident SalesSet

where Year([Order Date])=2013

Group by Manufacturer, [Product Name], Region, Segment;


INNER JOIN


LOAD

Manufacturer, [Product Name], Region, Segment, Sum(Sales) as "Sales2014"

Resident SalesSet

where Year([Order Date])=2014

Group by Manufacturer, [Product Name], Region, Segment;

Sales.PNG

I want  to discard these rows with null sales 2013 andnsales2014, what should I do.

Thanks nagaiankjimhalpertmtoarvind654bpnafderika

4 Replies
shiveshsingh
Honored Contributor

Re: INNER JOIN Doesn't filter Data and brings unwanted data

You can concatenate the data

CustomJOINTable20132014:

LOAD

Manufacturer, [Product Name], Region, Segment, Sales, '2013' as Flag

Resident SalesSet

where Year([Order Date])=2013

Group by Manufacturer, [Product Name], Region, Segment;


Concatenate


LOAD

Manufacturer, [Product Name], Region, Segment,Sales , '2014' as Flag

Resident SalesSet

where Year([Order Date])=2014

Group by Manufacturer, [Product Name], Region, Segment;




and then use expression as  Sum({<Flag = {'2013'}>}Sales),Sum({<Flag = {'2014'}>}Sales)

MVP & Luminary
MVP & Luminary

Re: INNER JOIN Doesn't filter Data and brings unwanted data

Have you Dropped the SalesSet table from your data model?

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

qliksus
Valued Contributor II

Re: INNER JOIN Doesn't filter Data and brings unwanted data

if you SalesSet table has both the year record why don't you just do it with one load statement like the below

LOAD

Manufacturer, [Product Name], Region, Segment,

Sum(if( Year([Order Date])=2013 ,Sales)) as "Sales2013",

Sum(if( Year([Order Date])=2014 ,Sales)) as "Sales2014"

Resident SalesSet

Group by Manufacturer, [Product Name], Region, Segment;

cravenrealtor
New Contributor

Re: INNER JOIN Doesn't filter Data and brings unwanted data

‌There are a couple of considerations to answer your question. The easy answer is you can simply ad a null filter at the end of your script such as:

Where not isnullI(Sum(Sales)) ;

This would work because you have the same null records in both data set.

I think the broader point that was being made before is why are you trying to do a join? Just simply for YoY so that you can do a simple calc such as YR2013 - YR2014?

To the point made earlier, if this is the case you don‘t need to do a Join. You can simply use the Concatenate statement and this will function like a UNION since your data is the same. Note: however in your example you would be concatenating the same table. If this was just for example pupose and you actually have two separate sets this approach makes sense, if not all you need to do is flag your data as current year and prior year etc. Here is an example of how to setup a Master Calendar: Creating A Master Calendar 

If you use this approach instead, you can flag your data and easily compare two years, then you don’t have to worry about extra rows and mismatch joins and many other problems that can come from trying to get Qlik to behave like SQL.

Hope that helps!

Good luck