topic Re: Join Issue? Chart works when filtered but not if cleared in QlikView

Join Issue? Chart works when filtered but not if cleared

cbaqir — Thu, 03 Dec 2020 21:38:34 GMT

My charts are supposed to show Available Checkbook Hours vs Actual Hours by Month, Division, Work Name or Task Type. If I filter on CHECKBOOK_MONTH to 11/1/20, the data in the top graph looks good. If I clear the filter, the data does not look right. I think it's a join issue but I don't know how to fix it.

APP_QV_RCM_FINAL table contains the same fields but with different names to prevent multiple joins so which fields do I use in the charts?

The EPIC_CHECKBOOK table joins to the APP_QV_RCM_FINAL table on a concatenated key %CHECKBOOK_MONTH_MODULE of Month, Work Name, Division and Task Type.

For example, in the EPIC_CHECKBOOK table the field is Division but in APP_QV_RCM_FINAL it's DIVISION.

in the EPIC_CHECKBOOK table the field is Module but in APP_QV_RCM_FINAL it's WORK_NAME.

in the EPIC_CHECKBOOK table the field is CHECKBOOK_MONTH but in APP_QV_RCM_FINAL it's TRX_DATE.

in the EPIC_CHECKBOOK table the field is TASK_TYPE_OPT but in APP_QV_RCM_FINAL it's TASK_TYPE.

Re: Join Issue? Chart works when filtered but not if cleared

edwin — Thu, 03 Dec 2020 17:35:09 GMT

some of the expression are aggr by checkbook month but you dont use that in your dimension. i think you wanted it aggr by division only so remove checkbook month and try that. the reason it works when month is selected i bec aggr by a single dimension value returns a single result.

after doing that does the chart show expected results when filters are cleared? if not, what is the expected value when the filters are cleared?

Re: Join Issue? Chart works when filtered but not if cleared

edwin — Thu, 03 Dec 2020 17:40:57 GMT

actually, if on the first chart you need total by division you dont need to AGGR it just straight sum.

Re: Join Issue? Chart works when filtered but not if cleared

edwin — Thu, 03 Dec 2020 17:44:19 GMT

even for the horizontal bar chart, since you have both dimensions - you dont need to AGGR them. AGGR will only make it slower and you get the same result anyways

Re: Join Issue? Chart works when filtered but not if cleared

edwin — Thu, 03 Dec 2020 17:56:37 GMT

also note that some of your dates arent properly formatted, it may not join properly.

1/1/2021-Epic OPT ECSM--
01/1/2021-Epic OPT ECSM-ND-Delivery
01/1/2021-Epic OPT ECSM-NE-Delivery
01/1/2021-Epic OPT ECSM-PNW-Delivery
01/1/2021-Epic OPT ECSM-TX-Delivery
1/1/2021-Epic OPT EMFI--
01/1/2021-Epic OPT EMFI-ND-Delivery
01/1/2021-Epic OPT EMFI-NE-Delivery
01/1/2021-Epic OPT EMFI-PNW-Delivery

Re: Join Issue? Chart works when filtered but not if cleared

edwin — Thu, 03 Dec 2020 18:09:09 GMT

this part caused it:

NoConcatenate
APP_QV_RCM_FINAL:
LOAD *,monthstart(date(TRX_DATE,'MM/D/YYYY'))&'-' .....

it should be date(monthstart(TRX_DATE),'MM/D/YYYY')