Splitting Multiple Values in a Single Cell to Many Name Columns

[lilclie]

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]

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]

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]

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]

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]

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]

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]

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]

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

 

[theDBguy]

Yes I can. I'll upload one later tonight

Excellent. Thanks!