topic Re: Stuck with SQL problems in QlikView

Stuck with SQL problems

Tue, 25 Mar 2014 09:26:15 GMT

Hello! I have a trouble, and would be really happy if you could help me

First, this i my SQL selections from a ODBC.

This works perfect. I make plots of this to see different answ_call_cnt and aband_call_cnt with the different queues. And i make selections with Year, month and day. Nemas problemas!

But my Table viewer is quite hard to understand.

Because now i would like to import some agent_name from the Agent_logg. See picture below.

But i don't get it to work 😕

Any input would be greats. I can't post a sample file because im on a private license and connected to ODBC.

Re: Stuck with SQL problems

Tue, 25 Mar 2014 10:21:44 GMT

You don't appear to have anything to link the agent_logg_id back to any of your data tables.

I would assume the Queue_Logg table would contain and advisor id or ref, so you would need to update the queue_logg sql to pull through that, and also the agent_logg table to join.

The Queue is not enough in your model to pull agent info.

Re: Stuck with SQL problems

flipside — Tue, 25 Mar 2014 10:39:03 GMT

I would say the issue is the field date_from which appears in each table - it is causing sythentic keys in the load script. Is this a common link field between each table? Remember, Qlikview automatically creates joins between tables based on field names.

flipside

Re: Stuck with SQL problems

its_anandrjs — Tue, 25 Mar 2014 10:49:33 GMT

Hi,

In your data model a synthetic keys generated due to same field names like date_from or queue if you want to increase a performance remove any field from any table if not required or simple rename it in that table. And for agent_name you have to find the Agent_logg_id field among the different table that available or not then you link it or join with that table.

Hope this helps

Thanks & Regards

Re: Stuck with SQL problems

Tue, 25 Mar 2014 11:36:16 GMT

I have the Agent_logg_id in the Service_goals table. Where i get my goal_desc and goal_id from.

Re: Stuck with SQL problems

Tue, 25 Mar 2014 11:38:05 GMT

Here is the Queue_logg table, i have tried different solutions, but can't get it right.

Thanks for you effort and answers!

Re: Stuck with SQL problems

Tue, 25 Mar 2014 11:38:51 GMT

Yes, but i think i need to have these 2 days_from, because when i make selections in "Year" "Month" "Day" i want the right connections!

Re: Stuck with SQL problems

jonathandienst — Tue, 25 Mar 2014 12:19:07 GMT

Perhaps you should be joining Queue_logg to Goal_results - like this:

SQL .... FROM Goal_Results;

Join

SQL .... FROM queue_logg

This will correctly link the data between the two tables if the relationship between these two tables is properly described by queue and date_from. If that is not the case, then you will need to bring in enough data to associate the tables correctly.

Joining like this will eliminate one table and the syn key from your model.

HTH

Jonathan

Re: Stuck with SQL problems

flipside — Tue, 25 Mar 2014 12:32:35 GMT

I don't think we have enough info on what the data is. The table Goal_Results looks like it is pre-aggregated data (judging by the _cnt fields = count?), that is one row for each combination of queue and date_from, whereas queue_logg could be multiple rows per queue and date_from (unless it is also aggregated at that level). Could end up with incorrect aggregation in the front end.

Erik, are my assumptions correct?

flipside

Re: Stuck with SQL problems

Tue, 25 Mar 2014 12:45:35 GMT

There's not (IMO) enough data being loaded from the source - and the data that appears above has no direct link back to the agent_logg table,

As part of the load script, the agent_logg needs to be pulled in then joined with the agent_logg_id to the respective table, otherwise it's not possible.

Re: Stuck with SQL problems

Tue, 25 Mar 2014 13:50:35 GMT

I think you are right, can you show me how to do it? Because im a newbie regarding SQL and databases.

Thanks.

Re: Stuck with SQL problems

Tue, 25 Mar 2014 13:51:56 GMT

cnt = count is correct. But i think it is aggregated at each level.

Re: Stuck with SQL problems

Tue, 25 Mar 2014 15:24:32 GMT

You need to bring in the view that has the agent_logg_id - once you've loaded that into the QV script - the joins should be quite clear when you look at the other imported data and PK's

Just do a Load *, from a SQL Select * query, and you'll see all the fields.

I came from a call centre background and know how difficult it is tracking individual advisors to calls in a queue - the issue I had was the queue was measured on a vdn level, and advisors tracked on the skills - best thing though, is to being everything into QV and then disregard what you don't need.

Try to avoid syntheic keys where possible - they only cause issues further down the line.