Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Join still losing rows

Hi,

I haven't used Qlikview in a long time and relearning the very little I knew. I have been struggling for a long time on my problem.  I have a table with Sales $  by week, class and store.  I have Sales Targets by the same combo but not for all Stores.  Likewise the Sales table I obtain will have no row of data if a particular Week,Store,Class combination has $0 sales.  I can't join the two tables for this very reason because either table might be missing an essential row of data to join.  My solution was to create a placeholder table using a cartesian join.   For some reason I still cannot do a Join that retains all the rows when there is a $0 sale for a store, week, class. Its as if Left or Right Joins are not working for this specific table.  If I load the cartesian table I made I can verify that the data is there.  Has anyone seen this behavior?

Does the order of the table load matter? I can use Left or Right explicitly on table loaded 2nd correct?

6 Replies
Anil_Babu_Samineni

I believe you need to re study on Joins. Here, If we load order table with some null identifiers that means to club using Concatenate of that Cartesian table.

Can you help us to provide at least few rows of input from 2 table and expected result.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
petter
Partner - Champion III
Partner - Champion III

Normally it would be smart to have a store dimension table that has the complete list of all stores whether they have sales or not and whether they have targets or not. Then it is easier to have more store specific information in the store table too. Using a dimensional modeling approach like this is also a recommended practice. Often this is called a star-model. Often a date/calendar dimension in the form of a separate table is very useful.

You could have three tables: Calendar, Store and SaleTarget (combining the two since they share all dimensions).

If you find this approach interesting I could elaborate and explain more.

Anonymous
Not applicable
Author

Yes,

I agree. I am using a star model and it would be ideal to have a $0 sales row instead of none but this is how the data comes in unfortunately.  Shouldn't my cartesian with all dimensions in the key (week ending date, store and product class) included but no metrics as a placeholder work as an alternative? I would then left join my Sales table to that and then use the resultant table as a left for my target?

Anonymous
Not applicable
Author

I've never had a problem with joins until qlikview. I initially had a problem with this join because the date part of my key was in date/time format and another table was in serial. I still don't know why that would have occurred when it was from the same source but I fixed it.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Why not do an OUTER JOIN? You will lose no rows.

-Rob

Anonymous
Not applicable
Author

Isn't that just using the "Join" word by itself?  If so that's what I was doing initially but gave up. I may have to revisit. Something isn't making sense. I'll experiment more and then post again.