topic Cross Join in QlikView

Cross Join

Wed, 25 Nov 2020 16:16:04 GMT

Hello Community.

I have a table with the and sales.

I would like to join that table to calendar that contains all the date from 01/01/2010 up to today, but I day to be joined to every row in the sales table even when we did not have sales at that date.

For example if I had sale on 01/02/2012 this row will be joined to every date in Calendar.

Hope that make sense.

In SQl I would do it with cross join but cannot find a way to make it in qlikview.

Can you kindly help me?

Thanks.

Re: Cross Join

engishfaque — Mon, 31 Oct 2016 18:40:47 GMT

Hi Denis,

Here it is,

temp:

Load *

From yourSalesTable;

Joinv(temp)

Load *

From yourDateTable;

Kind regards,

Ishfaque Ahmed

Re: Cross Join

Mon, 31 Oct 2016 18:46:22 GMT

Perfect!

Thanks Ahmed!

Re: Cross Join

johnw — Mon, 31 Oct 2016 18:51:02 GMT

Have different names for the dates in the sales table and the dates in the calendar table, and every row in sales is "joined" to every row in calendar without the need to do anything further. You can join them together into one table as you're asking and as Ishfaque shows, and there are sometimes reasons to do so, but you'd want to be sure that was required first, because that could make a very large table.

Re: Cross Join

Mon, 31 Oct 2016 19:12:39 GMT

Thanks for warnings John.

I am aware of the size problem but I still have to do it due to requirements of the report.

The data is not too big so it should be fine.

Thanks

Denis