Splitting Multiple Values in a Single Cell to Many Name Columns (1 Viewer)

lilclie

New member
Local time
Today, 09:19
Joined
May 23, 2019
Messages
5
Hi Community,

We have an external data source that may have a random combination of values that are separated by semi-colons. I would like to transpose this information into a table where a single value has dedicated column and if present would show 1 for Yes, 0 for no. Is this possible?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:19
Joined
Oct 29, 2018
Messages
21,357
Hi. Yes, it's possible, but it doesn't sound like doing it the way you're thinking would make it a properly designed database. Instead, you could think about splitting the values into separate records (rows) rather than individual yes/no columns.
 

lilclie

New member
Local time
Today, 09:19
Joined
May 23, 2019
Messages
5
Hi. Yes, it's possible, but it doesn't sound like doing it the way you're thinking would make it a properly designed database. Instead, you could think about splitting the values into separate records (rows) rather than individual yes/no columns.

Current constraints with the design of the external data and the interpretation of that data do need an overhaul. I have to workaround them for the time being.

Are you able to provide a solution into multiple columns?

Thank you in advance.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:19
Joined
Oct 29, 2018
Messages
21,357
Current constraints with the design of the external data and the interpretation of that data do need an overhaul. I have to workaround them for the time being.

Are you able to provide a solution into multiple columns?

Thank you in advance.
I see. I am not sure there is an automatic or built-in way to do it, so I am thinking you'll need a custom approach. Would you be able to post a sample db for us to play with?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:19
Joined
Feb 28, 2001
Messages
26,998
This sounds like you would need, in essence, a brute-force method. Are we talking a text file (.TXT or similar) with variable-length records?

It is also not clear what you wanted to see coming out of this process. Can you fake up a few sample records of input and what you might want for output?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:19
Joined
Feb 19, 2013
Messages
16,553
if the values are known e.g.

datafield
a;b;c
d;e;a
b;c
a;d

etc

then in a query the calc for the 'a' column might be

a: abs(instr(datafield,"a")>0)

substitute "a", with "b" etc for the other columns

to make it fully unique then use

a: abs(instr(";" & datafield & ";",";a;")>0)

or use a function to achieve much the same thing
 

lilclie

New member
Local time
Today, 09:19
Joined
May 23, 2019
Messages
5
Thanks for the answers everyone. Instead of building something custom, I will take the suggestion of DBguy and move to a column/row solution.

DBguy, if you are still available, are you able to assist?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:19
Joined
Oct 29, 2018
Messages
21,357
Thanks for the answers everyone. Instead of building something custom, I will take the suggestion of DBguy and move to a column/row solution.

DBguy, if you are still available, are you able to assist?
Hi. Yes, but I asked if you could provide a sample db, so we can have a better idea of what you need. (Rather than me just making something up, which you then say you can't use.) So, are you able to provide it?
 

lilclie

New member
Local time
Today, 09:19
Joined
May 23, 2019
Messages
5
Hi. Yes, but I asked if you could provide a sample db, so we can have a better idea of what you need. (Rather than me just making something up, which you then say you can't use.) So, are you able to provide it?


Yes I can. I'll upload one later tonight
 

Users who are viewing this thread

Top Bottom