topic Multiple values in single field in QlikView

Multiple values in single field

jatishqv — Tue, 18 Aug 2009 23:36:01 GMT

Hi there,

I am having trouble figuring out how to tackle this issue. I am using OLEDB connection to a .mdb that has various fields that have mutilpe values in a single field. These multiple values are delimited by a double character space.

I am able to use the subfield function to seperate the field, but this causes extra rows to be added to the dataset.

I.e

ID number,Mutiple_Field,Regular_field,

1, Apples Carrots,Mike,

2, Apples Orange,John,

3, Kiwi Grapes,John,

Without using the subfield function on 'Mutiple_Field' and I did a frequency count on regular field in would got Mike =1, John = 2,

When using the Subfield function, subfield(Mutiple_Field, ' ') as Multiple, I would get Mike =2 and John = 4, the frequncy count for Mutiple_field would be correct, as long as I only used one subfield function.

To summarize I would like the mutiple_field to be handled seprately so it does not affect other fields in the DB.

I hope that all makes sense, if anybody needs any more info to be of assistance please get back to me.

Thanks in advance.

Jat

AW:Multiple values in single field

biester — Tue, 18 Aug 2009 23:53:00 GMT

Hi Jatish,

I think I don't see what you are intending to do. You could make a loose table with the subfield split, but there wouldn't be a join with the regular field or the ID any more of course. Could you perhaps show the intended result?

Rgds,
Joachim

Multiple values in single field

hector — Wed, 19 Aug 2009 00:03:37 GMT

Hi, if you split the table in ID Number / Multiple field (with the subfield() function without column as you did) and ID Number / Regular Field (a name apparently)

Regards

Multiple values in single field

Anonymous — Wed, 19 Aug 2009 09:19:24 GMT

Jat,
The natural way to go is to keep it in two separate tables:
1. ID number, Mutiple_Field, Regular_field
2. Mutiple_Field, Multiple
So, the counts will be clean. The Mutiple_Field is the link. If you wish, you can convert it into something more compact, for example by using autonumber.
The less natural but usable way is to leave as it is in your example, but use aggr in count:
aggr(count(whatever), ID number)

Multiple values in single field

jatishqv — Wed, 19 Aug 2009 18:44:16 GMT

Thanks for the replies! Michael I think you hit the nail on the head.

Where I was going wrong was, using the ID number as the key field, instead of the Multiple_Field.


LOAD `Multiple_Field`,
'Normal_Field_1`,
'Normal_Field_2`,
'Normal_Field_3`,
 ID;
SQL SELECT *
FROM `PRF_Data`;

Load_Multiple_Field:
LOAD `Multiple_Field`,
subfield(`Multiple_Field`, ' ') as Multiple_ Field_s;
SQL SELECT `Multiple_Field`
FROM `PRF_Data`;

Apply_Multiple_Field:
Load Multiple_ Field_s,
ApplyMap('Map_Call_Info', Multiple_Field_s) as 'Multiple Field'
Resident Load_Call_Info;

That is an example of the code I am using as I need to map the numerical values to real life values, and this works a treat. I'm not sure if its the best way but it does work.

Thanks a lot Michael!!

Multiple values in single field

jatishqv — Fri, 21 Aug 2009 21:02:58 GMT

It appears I spoke to soon.

The multiple_field splits ok, and making a selection on that list box affects other fields as it should. But if I clear the selections and make a selection on another 'regular' list box, the changes aren't reflected on the multiple_field list box.

I have attached a copy of the .qvw and would be grateful if someone could have a look and let me know where I am going wrong.

Many thanks

Jat

Multiple values in single field

jatishqv — Tue, 25 Aug 2009 18:08:29 GMT

Hi,

I still haven't found a resolution to this problem. Any thoughts?

Multiple values in single field

Anonymous — Wed, 26 Aug 2009 09:11:47 GMT

Jat,
I didn't notice anything wrong in you application. Can tell what exactly doesn't work as you expect?