Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Phunshey
Contributor II
Contributor II

Date Fields of Fact Table and Connected Table Showing Null

Hello, I have just started using Qlikview a few weeks ago. I am looking for a help from expert Qlikview users.

I am working to create simple dashboards. I have two excel files; one include records of number of car accidents and the other one for number of car movements. I have connected the tables in the following way:

Car_Accident_Data:
LOAD

Accident_ID,

Accident_Date,
Department,
Accident_Category,
Sub_Category,
Title,
Car_No,
Model,
Location,
NUM(Date(floor(Accident_Date),'DD/MMM/YYYY')) AS DateValue

FROM
[Working Files\Occurrence_Data_Main.xlsx]
(ooxml, embedded labels, table is Car_Accident_Data);


Car_Movement_Data:
LOAD
Movement_Date,
Car_No,
DEP,
ARR,
NUM(Date(floor(Movement_Date),'DD/MMM/YYYY')) AS DateValue

FROM
[Working Files\Flight_Cycles_Main.xlsx]
(ooxml, embedded labels, table is Car_Movement_Data);

I have the following as Master Calendar;

LET varMinDate = Num(Peek ('Accident_Date',0,'Car_Accident_Data'));
LET varMaxDate = Num(Peek('Accident_Date',-1,'Car_Accident_Data'));
LET varToday = Num(Today());

//************TempCalendar***********
TempCalendar:
LOAD $(varMinDate) + rowno() - 1 AS DateNumber,
date ($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE $(varToday) - $(varMinDate) + 1;

//*************Master Calendar************
CAL:
LOAD TempDate AS Date,
//TempDate AS FLT_Date,
//TempDate AS Occ_Date,
NUM(Date(floor(TempDate),'DD-MM-YYYY')) AS DateValue,
Week (TempDate) AS Week,
Year (TempDate) AS Year,
Month (TempDate) AS Month,
Day (TempDate) AS Day,
Weekday (TempDate) AS WeekDay,
'Q' & Ceil(Month (TempDate)/3) AS Quarter,
Year (TempDate)&'-Q' & Ceil(Month (TempDate)/3) AS YearQuarter,
Date( Monthstart (TempDate), 'MMM-YYYY') AS MonthYear,
Week (TempDate) & '-' & Year (TempDate) AS WeekYear

Resident TempCalendar
Order by TempDate ASC;
Drop Table TempCalendar;

I am not sure what wrong I am doing here... when there is no accident data (Count_Accident), the date fields  of both Accident_Date and Movement_Date show Null. In turn Count_Car_Movements also show Null (shows 0 here as I have checked Show All Values under Dimensions), even though there are car movements every day. So when there is no accident, I must have number of car movements to calculate rate, such as accident rate based on total car movements per month, quarter, and year. Due to this issue, I am not getting the correct rate. 

Phunshey_0-1619544588363.png

Can any experts help me sort this issues? I have been trying and looking around in the internet for a week now. 

Thank you advance. 

 

1 Solution

Accepted Solutions
edwin
Master
Master

you shouldnt use {1} as you experience it will ignore all your selections.

as i said use {<Department=>} only for the calculation of movement and  not accidents (as you want to be able to filter accidents by department and not filter movements

View solution in original post

12 Replies
edwin
Master
Master

can you load a QVW even with small made up data?

Phunshey
Contributor II
Contributor II
Author

Hello Edwin, thank you for your time. Please find attached the QVW file with a made up data.  Looks like I was not 100% clear in explaining my problem before. Now I will explain here further. 

If you select May-2020 from MonthYear, I get correct Count_Car_Movement count and the total. This is how I want the data to be. However, when you select any from the Department, the Count_Car_Movement changes differently which should not be the case for my rate calculation.  Weather there is car accident or not, the Count_Car_Movement count and total should remain same for the month and day irrespective of any Department I select. 

Hope you understand my explanation 🙂 

Thank you for your assistance. 

edwin
Master
Master

the reason it changes is because your link is on DAY granularity not MONTH level.  as you select departments, specific DAYS are being "possibly selected"  thus MOVEMENT will change.  the quick and dirty here is decide if you will allow the user to select only MONTHS and not DAYS.  use MONTHSTART in your calculation of datevalue (link by month not dates)

Phunshey
Contributor II
Contributor II
Author

Hello Edwin, thank you for your help. Now the issue has solved for Day. Users don't need to select by day, they only need to select month, quarter or year. The problem now is, when there is no accident in any month, the same issue appears. Is there any work around that I could do to the resolve this issue whether the users select day, month, quarter or year? Please find below the updated script for your review. Thank you for your continued assistance. 

Accident_Data:
LOAD Accident_ID,
//Accident_Date,
Date(MonthStart(Accident_Date), 'MMM-YYYY') AS Accident_Date,
Department,
Accident_Category,
Sub_Category,
Title,
Car_No,
Model,
Location
FROM
Car_Accident_Data.xlsx
(ooxml, embedded labels, table is Sheet1);

Movement_Data:
LOAD //Movement_Date,
Date(MonthStart(Movement_Date), 'MMM-YYYY') AS Movement_Date,
Movement_ID,
CAR_No,
DEP,
ARR
FROM
Car_Movement_Data.xlsx
(ooxml, embedded labels, table is Sheet1);

edwin
Master
Master

the reason that happens is there are no rows associated to the table ACCIDENTS when the department is selected.  therefore no month is selectable.  your data does not have this scenario so without understanding you specific case, i would suggest try ignoring department in your calculation for movements.  

add set analysis:

Count({<Department=>}Movement_ID)

 

yassinemhadhbi
Creator II
Creator II

Good morning

The first impression when i see your load script that you have two commun field that will automatically create syntetic Key , which causes many problemes

So the first the thing u need to do ios that u have to rename One of the these two columns ( Car_no or Date_Value) and see if the problem persists

Best Regards
Yassine Mhadhbi
Phunshey
Contributor II
Contributor II
Author

@edwin . Thank you for your continued assistance. Selecting department is one of the main requirement for my dashboard. 

I have found that using this expression "Count({1}Movement_ID)" gave me what I want. The new problem is, when I select May-2020 from the listbox, other months are also showing in the table, which in turn also show as bar in my bar chart. 

I have attached the updated QVW file. Appreciate your further assistance. 

Phunshey
Contributor II
Contributor II
Author

@yassinemhadhbi Thank you. I tried but no joy. Appreciate if you could assist me with a copy of script here. 

edwin
Master
Master

you shouldnt use {1} as you experience it will ignore all your selections.

as i said use {<Department=>} only for the calculation of movement and  not accidents (as you want to be able to filter accidents by department and not filter movements