divided data as one column in different fields?

Fri, 13 Jan 2012 12:28:17 GMT

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

divided data as one column in different fields?

swuehl — Fri, 13 Jan 2012 12:53:09 GMT

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

topic divided data as one column in different fields? in QlikView

divided data as one column in different fields?

divided data as one column in different fields?