topic Re: Data Missing due to Date Interval in QlikView

Data Missing due to Date Interval

Sun, 07 Dec 2014 12:28:37 GMT

Hi All,

I have been trying to solve this but still cannot manage to solve this.

Scenario:

I am loading this set of data consisting of 19 records for 3 students.

Below is the screen capture.

You can also refer to attachment: Oct 2014 DOPS.xlsx for raw data.

However, after I loaded into the dashboard. I only manage to see 7 records out of 19 records.

After trying to resolve, i suspect the issue lies with date interval on the evaluation start date and evaluation end date. This evaluation is linked to the [evaluationperiod] found in Dates Table.

I can't solve, so i am posting online here for help. Had also attached the qvw and the rotation master for your reference.

In Rotation Master, there are 3 fields; Rotation period, Start Date, End Date. The raw data excel with evaluation start date and evaluation end date is interval match to the Start Date and End Date.

When the Evaluation Start Date and Evaluation End Date overlapped across 2 months. The data seems to be missing. Appreciate if someone can take some time in resolving this.

Edit: In addition, please go to Dashboard -> Evaluations Tab -> Click on DOPS under Evaluation AND Click on Raw Score.

You should be able to see the data in the dashboard and compare it with the raw data excel file.

Thanks

Re: Data Missing due to Date Interval

prieper — Sun, 07 Dec 2014 18:32:00 GMT

Hi,

think that the problem is only the expression - MAXSTRING(...) does only deliver few results, NULL-values are not displayed.

Not quite clear, what the intention of this formula is?

HTH Peter

Re: Data Missing due to Date Interval

Mon, 08 Dec 2014 01:36:49 GMT

Hi Peter,

the formula is just to show all the data in the table. When this expression is been used:

=QuestionData

No data is shown. That why we changed it to MaxString(...)

Do let me know if you have any other questions.

Thanks

Chun Wey

Re: Data Missing due to Date Interval

prieper — Mon, 08 Dec 2014 10:01:20 GMT

You are correct:

I would check the first INTERVALMATCH:

are the data correct/complete?

Do all dates have the correct format, so that they can be interpreted as numbers? Are there probably some decimal values included (timestamps=?

HTH Peter

Re: Data Missing due to Date Interval

Wed, 10 Dec 2014 01:14:18 GMT

Hi Peter,

Apologies for late response.

I have checked the date format; there are no decimal values in them and the data seem correct.

Can kindly advise on what other areas to look at ?

Attached data in excel for your reference.

Thanks

Chun Wey

Re: Data Missing due to Date Interval

prieper — Wed, 10 Dec 2014 09:50:14 GMT

Dear,

you need to isolate the problem. Attaching the entire application is not quite helpful as it is very complicated to understand for an outsider.

Understand that you need to link the assessment-table (DOPS) with Rotation-Master?

You can link only for one date into a period, the DOPS has actually three fields (which of them should be taken?), the Rotation-Master - as per the attached Excel - has start- and end-dates, which are not overlapping.

Usually I do not rely on the end-dates in a table, but calculate it to be 1 day before the next period starts (honouring possible keyfields, though).

Re: Data Missing due to Date Interval

Thu, 11 Dec 2014 06:37:42 GMT

Hi Peter,

Apology if I had caused any misunderstanding or confustion. My intention of attaching application is that sometimes there will be people requesting for application to look at the problem at ground level. So I normally will attach at start for easy reference.

Thanks for your time in helping to look at this. As for your questions, the responses are below.

Understand that you need to link the assessment-table (DOPS) with Rotation-Master?

Ans: No. Rotation-Master is been concatenate into a table called Dates. So the assessment-table is linked to this dates-tables(Rotation-Master).

You can link only for one date into a period, the DOPS has actually three fields (which of them should be taken?

Ans: The field is called 'EvaluationPeriod'

The excel file i attached is the data extracted from the application. Looking at it, i found no problem on the format wise nor any missing data. Thus, I have no idea where to further look at to troubleshoot. It will be good if you or someone else can guide or help on this portion.

Thanks again for your time.

Rdgs,

Chun Wey