INNER JOIN Doesn't filter Data and brings unwanted data

[Anonymous]

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;

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

Thanks nagaiank‌ jimhalpert‌ mto‌ arvind654‌ bpn‌ afd‌ erika‌

[shiveshsingh]

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)

[rwunderlich]

Have you Dropped the SalesSet table from your data model?

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[qliksus]

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;

[Anonymous]

‌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