topic Re: Assist with script please in QlikView

Assist with script please

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

hello,

Please find attached.

so my model contains Table volumes, per day and Hour.

I need to visually identify, where there is data missing. So I would need to join to some Master table that has the whole range of dates that my data contains, plus all time ranges in hour (from 00 to 23), to my fact table (Data can be missing for a WHOLE day, or only for a few hours per day)

I have attempted to do that join in my model, but I've hit a mindblock. You will see that table "HF WOWH - LBSBKM" is missing data for the 6th,13th and 17 th.

I dont know how to do the join that will help me identify these null fields from the join - my logic tells me I need to join my fact table to the Calendar, and then test for nulls...

Please help

Re: Assist with script please

Miguel_Angel_Baeyens — Fri, 18 Sep 2020 14:10:20 GMT

When null values displayed on charts or objects come from missing data (e.g.: in your case, let's say there were no transactions whatsoever on the 6th, no lines in the data source have a date of 6th), you will need to "generate" that data.
That can definitely happen by joining to the calendar table, however, depending on the size of your data model, that could be a bit of an overkill, and leave a massive table with more fields than those you actually need.
However, I strongly recommend you take a look at Henric Cronström's posts here:
- https://community.qlik.com/t5/QlikView-Documents/NULL-handling-in-QlikView/ta-p/1484472
- https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394

Re: Assist with script please

QFanatic — Fri, 18 Sep 2020 15:47:25 GMT

hi Miguel, Thank you for your response.

I have read through Henric's info and I think using the below could work (using the combination of Date and Hour in both tables)? As you said - table volumes might be an issue - also because I would need to add Table_Name to that %Key field as well...what do you think?

Re: Assist with script please

Brett_Bleess — Wed, 14 Oct 2020 14:53:49 GMT

@Miguel_Angel_Baeyens just shouting out, as it appears notifications are not working well yet in Community, poster had one final scenario they wanted to run by you, and give you are a much better developer than am I, figured I would let you chime in on your thoughts.

Regards,
Brett

Re: Assist with script please

Miguel_Angel_Baeyens — Wed, 14 Oct 2020 15:21:42 GMT

Using Exists() in the way you mentioned is exactly one of the ways to very easily select those customers that do not have any orders for any of the time periods loaded in your data model.

If that covers your use case, go ahead and use that, it shouldn't be an expensive table to load.

However, this alone will not solve the issue of displaying "20200906" in your tables and charts if there is no data for them, and that date will be missing from the UI.

You can then insert data in the data source (if possible), for at least one customer for that date with value 0

Depending on the objects, you can set a continuous axis (e.g.: https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Bar_Chart.htm section "Dimension Axis") and display null values or all values (same link but section "Chart Properties: Dimensions") and the datapoint for those values, assuming the dimension is from the Calendar table, will display 0.

"Show All Values Enable this check box to show all the dimension values regardless of the selection. As the expression value is zero for excluded dimension values, the option Suppress Zero-Values in the Presentation page must be deselected for Show All Values to work. Show All Values does not apply if you use an expression as dimension."

This might not work in every chart and in every case, so you will still have to generate that data if you still want to display the missing "20200906" and all columns to "0".

In your case, assuming the field REQ_DATE is in the calendar table, which will return all dates between the minimum and the maximum from the table "Data_Temp", regardless whether they have any data, it should work.

But in the app you attached, since you are LEFT JOINing the MasterCalendar table to the Data_Temp, those missing dates will still be missing (they are not on the LEFT part of the JOIN, the JOIN will drop them).

Leave the full calendar table associated with the fact table by using the REQ_DATE or a numeric equivalent (e.g.: first date in your data model = 1, second date = 2, etc.).

Thanks @Brett_Bleess for the shout!

Re: Assist with script please

QFanatic — Wed, 14 Oct 2020 16:08:15 GMT

Hi Brett,

A very clever colleague of mine helped me to do this in script.

So I'm not sure if I should mark this as solved...it has been solved, but not via here.

Thank you

Re: Assist with script please

QFanatic — Wed, 14 Oct 2020 16:21:49 GMT

Thank you Miguel, much appreciated.

Re: Assist with script please

Brett_Bleess — Wed, 14 Oct 2020 17:04:30 GMT

@QFanatic What you can do is unmark Miguel's post as the solution, and post what you did instead and then mark that in the case of you having done something differently, but it is up to you. Generally giving posters a 'Like' for the assistance is nice in these cases too even though they did not actually solve things for you. Hopefully makes sense.

Regards,
Brett