topic Re: Add item for each unique entry in QlikView

Add item for each unique entry

nsm1234567 — Sun, 24 Feb 2013 16:56:10 GMT

Hey there,

I've been battling quite a bit with an issue I'm also battling to articulate. The below table represents my data once it's imported into QV. The below is a single entry from a form used to capture data. So this is John Smith who made a sale for "MyCompany" and the reference for his sale is 123456.

I've then taken a second table that contains a list of all the consultants and joined this to the first table. The problem is that while "consultant" is now associated with John Smith, it's not associated with any of the related info. What I want to do is have each "value" associated with "John Smith" such that John Smith will appear in all three rows, rather than just the first one.

I'm battling to get this right. Does anyone have any ideas? In essence, the issue is that the "value" column contains all of the fields captured.

fieldId	value	itemId	name	consultant
49	John Smith	1	Salesperson	John Smith
50	MyCompany	1	Company	-
51	123456	1	Policy Reference	-

Re: Add item for each unique entry

Sun, 24 Feb 2013 17:04:44 GMT

If I am understanding you, it appears you already have the association you want. Can you describe your Table schema (or post screenshot of Table Viewer)?

Message was edited by: Donald Hutchins (to clarify response)

Re: Add item for each unique entry

Sun, 24 Feb 2013 17:43:20 GMT

It occurs to me that perhaps I misunderstood, and it's not an association you are after, rather you are Joining to create this table (as you picture it) in your Load Script. And if that is the case, post your script to help clarify.

Re: Add item for each unique entry

nsm1234567 — Sun, 24 Feb 2013 18:01:49 GMT

Hey there, thanks for your response. I've pasted the relevant sections of the script below. Options is a comma delimited list of consultant names. So I first create a table called "CombinedTable", then I'm delimiting "Options." I've pasted two screenshots to try and illustrate the issue. If I filter using the name of a consultant, I don't want to lose other items with the same itemId.

CombinedTable:

LOAD fieldId,

trackerId,

name,

options,

itemId,

value,

DateTimeCreated,

MonthCreated,

YearCreated,

created,

Auditor,

status,

lastModif,

lastModifBy,

itemId & fieldId & value as mappingKey

RESIDENT TrackerFields;

DelimitedOptions:

lOAD itemId & fieldId & SubField(options,',',ITERNO()) AS mappingKey,

SubField(options,',',ITERNO()) as ConsultantName

RESIDENT CombinedTable

WHILE(ITERNO()<=SubStringCount(options,',')+1) AND fieldId = 49;

Without select

With select

Re: Add item for each unique entry

phaneendra_kunc — Sun, 24 Feb 2013 18:09:24 GMT

Hello NSM....

Qlikview doesn't work like that,,,,Meaning your data will be filtered based on the selections you make...

So i would suggest you to update your script to associate missing "Consultant Names" wiht the one you have ...

Example...

Do a Mapping load and replace those NULL items with "Test user" or its relevent data...based on Item Num..

Here 111 associated with Test user..

Re: Add item for each unique entry

Sun, 24 Feb 2013 18:31:41 GMT

In the DelimitedOptions table, you create mappingKey using itemID + fieldid + (a consultant's name based on the iteration)...

But the mappingKey of your CombinedTable is itemID + fieldID + value.

I gather you want DelimitedOptions to be a list of Consultants, with mappingKey to be the link. But currently, that's only going to happen if [value] is the name of the Consultant.

Is there not a unique ID column for your CombinedTable? If so, use that, or use what you have, but you must be consistent and use the same in your DelimitedOptions table.

Re: Add item for each unique entry

nsm1234567 — Sun, 24 Feb 2013 18:37:26 GMT

Hi there, thanks for the response. I realise QV doesn't work that way, was just trying to illustrate my problem as I'm finding it difficult to articulate the issue.

That kinda gets to the crux of the issue I'm facing then. Consultants will capture a number of these daily (hundreds), so I don't think a mapping table is feasible. So for all the identical itemId's, I want to have the consultant's name so that I can filter using the ConsultantName and I'm really not sure how to achieve that.

The itemId is 111 in this case, but like I say, the table will be growing daily and so I won't be able to use a mapping table (I don't think).