Qlikview v look up (help needed PLEASE)

[Marianeedshelp]

My colleague posted something similar to this yesterday but he has forgot his log in so I am creating a new post (sorry) Below is his response/requirement for help to the community

Hi I was wondering if someone might be able to help me. I have used this forum for a while and want to thank all who have commented on various posts, I have found your suggestions and guidance extremely helpful. As a result, I have therefore decided to post my question here in the hope that someone can help me.

We manage a SharePoint site which records information about calls. In other words, an analyst will go in and fill in a form and save this to SharePoint. We have 2 analysts (say John and Jim) who observe cso officers (.which stands for client service officers) John has approximately 85 CSOs assigned to him and Jim has about 60 (rough estimates). Every month I have to compile 6 reports (these reports vary, some look at the total number of calls done, others look at the type of calls recorded, and so forth). Out of the 6 reports, 5 reports are now done automatically on qlikview. We simply refresh the data at the first of the month, and BOOM the reports are ready to go, however one report is doing my head in, which is why I am here.

Report 2, is basically a V look up which I do manually on excel. I get the CSO list from sharepoint, and export this to excel and then the assessment date. I then basically do a v look up to see if CSO ABC was observed in the month or not. If they were not observed I put them on the report. So in effect the report shows the audience which CSO’s Jim and John did not observe.

Currently I have the data loaded on QlikView but it is showing me all the CSO officers THEY DID OBSERVE. I want it to show the names of the CSO who they did not observe.

I don’t know if this is an issue in the data model, or the expressions, but it is really frustrating, I am 80% there at automating the process and would need that extra push to get me 100% there.

I appreciate all your feedback and thoughts on this.

Basically we need a V look up, we have two excel files (I have attached) the first file shows all the CSO officer names and who they are meant to be observed by. The second file shows the data from sharepoint (i.e., when the cso officers were observed). I get there names and do a search on the second file and if they appear as N/A it means they have not been observed). I then attach these to a excel report.

We want this to be done on Qlikview.

I have attached various screen prints and appreciate your help in getting this fixed.

[chrismarlow]

Hi,

Looking at your screenshots in the PDF you can generally show values from dimensions with no relevant facts by unchecking 'Supress Zero-Values';

This was done in a toy app with following script;

dimension:
LOAD * INLINE [
    LinkID, Name
    1, A
    2, B
];

fact:
LOAD * INLINE [
    LinkID, Value
    1, 1
];

If you have further issues may be better to post up what you think is going wrong with fragments of data/script, rather than what look like full source files.

Cheers,

Chris.

[Marianeedshelp]

Hi Chris 

Thanks so much for your response. I tried that but it never worked.

See attached source file i created. 

[chrismarlow]

Hi,

I only have personal edition, so I can't open that. I may however be able to help if you share the following;

1) For the object that is not returning the values even when Supress Zero-Values is off can you supply the definitions of the dimensions and expressions.

2) Can you also show what your data model looks like (ctrl-t should do it).

Cheers,

Chris.

[Marianeedshelp]

Hi thank you and please see attached documents.

Someone told me the following: 

Month  is missing in csoid when you joined CallMonitoring table. Joined CallMonitoring and cso tables with year and id.

Whatever month you are selecting on dashboard that is from CallMonitoring table not from cso table.

So month is missing in key.

[Marianeedshelp]

Dimensions 

[chrismarlow]

Hi,

Agree as soon as you make selections in either the date table or the fact table the supress fails to work as the set of data calculated over changes.

There may be a more elegant way of doing this but you can cancel the selections on a field in set analysis by including 'Field=', so in the below the grid on the left is just the grid on the right with the column with the Month selection removed hidden, the effect is both CSO associated with CSOObserver=X are shown, despite the month selection.

The weakness here is any other selection from your fact table (CallMonitoring) will collapse this, which makes me think there is a better solution.

Cheers,

Chris.

[Marianeedshelp]

Hi Chris

I appreciate your help so much. Thank you.

Do I add the 'Field=' in the expression on the tables?

[chrismarlow]

Hi,

Yes, your 'field' is Month (I think) & you need to add to the set analysis.

So I think you should try count(Month) and count({<Month=>}Month) & hide the second one. In the screenshot you on the PDF you also have a filter on 'Observer' that is in your CallMonitoring table.

Cheers,

Chris.

[Marianeedshelp]

Hi Chris, 

I created a small file on QlikView which shows the observers and CSO.

So if you clik on the CSO officer it shows when they were observed. Ideally i would like another column added just to show the names who were not obsereved, ie those who do not have a date.