Multiple values in single field

[jatishqv]

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

[biester]

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

[hector]

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

[Anonymous]

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)

[jatishqv]

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!!

[jatishqv]

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

[jatishqv]

Hi,

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

[Anonymous]

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