divided data as one column in different fields?

[#1]

divided data as one column in different fields, that are related in the same table? Attachment file. Thank you!

[swuehl · Accepted solution]

You could do it like this:

INPUT:

LOAD Customer,

     Sales,

     Com,

     if(trim(mid(Customer,3,2))='.-',trim(mid(Customer,5)),peek(Group)) as Group,

     if(trim(mid(Customer,3,2))='.-',1,0) as IsHeader

FROM

[.\Pregunta_Foro.xlsx]

(ooxml, embedded labels, table is Hoja1, filters(

Remove(Row, Pos(Top, 22)),

Remove(Row, Pos(Top, 21)),

Remove(Row, Pos(Top, 20)),

Remove(Row, Pos(Top, 19)),

Remove(Row, Pos(Top, 18)),

Remove(Row, Pos(Top, 17)),

Remove(Row, Pos(Top, 16)),

Remove(Row, Pos(Top, 15)),

Remove(Row, Pos(Top, 14))

));

Result:

LOAD Customer, Sales, Group, Com resident INPUT where not IsHeader;

drop table INPUT;

The important part are these two lines:

...

if(trim(mid(Customer,3,2))='.-',trim(mid(Customer,5)),peek(Group)) as Group,

if(trim(mid(Customer,3,2))='.-',1,0) as IsHeader

...

first is creating your Group by parsing the Customer string for the sub-string '.-', you may need to adapt the mid() function index and sub-string to look for to your actual data.

Second line is creating a flag to filter your table in a subsequent resident load, removing the header lines.

Hope this helps,

Stefan