topic Re: Join issue in QlikView

Join issue

sergio0592 — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I'm looking for display all value of time axis. In my sample in attached file, i have Nb Sales for January 2016. I want display in my chart all value of date, from 01/01 to 31/01. Even for days which no sales has occured.

Last year, i did that in an app with a left join in my load statement and it was ok. What goes wrong here?

Regards

Re: Join issue

rubenmarin — Fri, 16 Dec 2016 20:40:47 GMT

Hi sergio, using a left join only keeps the dates from the first table, you can use 'outer' instead of 'left' to keep dates from both tables.

Or better, generate a calendar: The Master Calendar

Re: Join issue

rubenmarin — Fri, 16 Dec 2016 20:51:18 GMT

You can create a calendar with this script:

ORDERS:

LOAD Date#(Date,'DD/MM/YYYY') as Date, Sales_nb, Seller INLINE [

Date, Sales_nb, Seller

02/01/2016,234,A

09/01/2016,867,B

13/01/2016,908,C

16/01/2016,345,D

19/01/2016,450,E

24/01/2016,345,F

25/01/2016,234,G

29/01/2016,255,H];

LET vMinDate=Num(Date('01/01/2016')); // This can be more dynamic

LET vMaxDate=Num(Date('31/01/2016'));

Calendar:

Load Date,

Month(Date) as Month,

Year(Date) as Year;

Load Date($(vMinDate)+iterno() -1) as Date AutoGenerate 1 While iterno() <= $(vMaxDate) - $(vMinDate);

QlikView joins tables using fields with same name, in the case of a calendar usually is kept in a separate table.

Also, if you want to know more about dates take a look at this posts:

Get the Dates Right

Why don’t my dates work?

Re: Join issue

sergio0592 — Fri, 16 Dec 2016 21:28:37 GMT

Hi, Ruben. thanks for your reply. I forgot to mention in my post that i have already tried with outer. The result is ok for a table box but not in charts (see my new attached file).

I'll try with creation a master calendar as you propose.

Re: Join issue

rubenmarin — Fri, 16 Dec 2016 21:35:09 GMT

Hi Sergio, in presentation tab-> uncheck 'Supress zero values'

Re: Join issue

alexpanjhc — Fri, 16 Dec 2016 21:36:51 GMT

check the Zero on bars, uncheck the Suppress Zero_values