Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shaimamahmoud
Creator
Creator

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
Master
Master

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
Specialist II
Specialist II

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
Contributor
Contributor

‌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