Query on Loading

[#1]

Hi guys, would greatly appreciate your help as I have been looking hard within this community for answers.

Let say one column of my raw data can contain the following:

Chicken; Duck; Pig; Rat

or it could simply be just Chicken; Pig

The number of animals within each cell can differ.

May I know how could I create columns on the fly to store each of the animal individually?

To illustrate my scenario again.

Before manipulation: Animals column contains {Chicken; Pig; Rat}

After manipulation: Animals column contains {Chicken}; Animals2 column (newly created) contains {Pig}; Animal3 column (newly created) contains {Rat}

Thank you.

[marcus_sommer]

You could use subfield to split the field-values, something like this:

load subfield(AnimalField, '; ') as SingleAnimal From YourSource;

whereby the field SingleAnimal is a kind of category which is most often more suitable as to distribute them into separate columns.

- Marcus

[#3]

Hi Marcus,

Thanks for your reply.

Sorry I may not understand this properly during my research, but isnt the use of load subfield creates new rows of data depending on the number of 'animals' I have within the original column.

[marcus_sommer]

Yes, that's correct - subfield will create a new row for each single value within the Animal field and like above mentioned is this most often easier to handle then to have a own column for each field-value. But of course could transform it (after the subfield-transforming) in this way per: The Generic Load

- Marcus