Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Greetings.
Is it possible to split data on the load coming in from excel in a single column (InputField) into multiple columns using the blank rows between questions as a delimiter?
Current...
InputField | |
____ Yes, we use this practice today |
|
____ No, we have a different practice in place | |
2. Question2? | |
____ Yes, they can do this independently in all scenarios | |
____ Yes, they can do this independently in some scenarios | |
3. xxxxxx |
Desired...
newcolumn1 | newcolumn2 |
Question1 ____ Yes, we use this practice today ____ No, we have a different practice in place |
Question2 ____ Yes, they can do this independently in all scenarios ____ Yes, they can do this independently in some scenarios |
|
You need to figure out the exact content that makes up the empty line. One possible solution is chr(10)&chr(13)&chr(10)&chr(13), i.e. line feed (LF) and carriage return (CR) in combination, twice. Each line break will also have one combination of those, i.e. chr(10)&chr(13), or at least one the characters. An empty line would then be two in succession.
If your data is in inputField, then you could try SubField(inputField, chr(10)&chr(13)&chr(10)&chr(13)).
Without seeing your actual data, these are however only speculations.
I think splitting it into n columns wouldn't be useful else I would create these 4 Q&A columns:
Q-Nr Type Content Content-Nr
1 Q Q1 1
1 A A1 1
1 A A1 2
and to get the information I would use interrecord-functions, like:
t: load *, rowno() as RowNo,
if(Type = 'Q', 1, if([Q-Nr] = previous([Q-Nr]) and previous(Type) = 'Q', 1,
peek('Content-Nr') + 1)) as [Content-Nr];
load *, recno() as RecNo,
if(isnum(subfield(InputField, '.', 1)), subfield(InputField, '.', 1), peek('Q-Nr')) as [Q-Nr],
if(isnum(subfield(InputField, '.', 1)), 'Q', 'A') as [Type],
replace(InputField, .......) as Content // may require n replace
from Source where len(trim(InputField));
Depending on the real data-set you may need some adjustments but the general logic should be working.