Unwrap table with six column pairs....?

[b_garside]

How would I unwrap a table that has 6 pairs of columns when it suppose to be 1 pair/two columns total vs 12.

My data looks like this;

UIC Level 01

TOE_NAME_LEV_1

UIC Level 02

TOE_NAME_LEV_2

UIC Level 03

TOE_NAME_LEV_3

UIC Level 04

TOE_NAME_LEV_4

UIC Level 05

TOE_NAME_LEV_5

UIC Level 06

TOE_NAME_LEV_6

Ideally it would be just two columns. But they were split.  The Unwrap wizard seems to only handle two column sets or one split?

I need to split it five ways then have it stack it.

Does this look correct.

LOAD  [UIC Level 01] as Hierarchy_UIC,
  TOE_NAME_LEV_1 as TOE_NAME

FROM
[_Application Data\Service Hierarchy.xlsx]
(ooxml, embedded labels, table is Page1_1, filters(
Unwrap(Col, Pos(Top, 3)),
Unwrap(Col, Pos(Top, 3)),
Unwrap(Col, Pos(Top, 3)),
Unwrap(Col, Pos(Top, 3)),
Unwrap(Col, Pos(Top, 3)),
Unwrap(Col, Pos(Top, 3)),
Remove(Row, RowCnd(Compound,
RowCnd(CellValue, 1, StrCnd(null)),
RowCnd(CellValue, 2, StrCnd(null))
))
));

Thanks All,

Brian

[pgrenier · Accepted solution]

Hello Brian,

Here is a code snippet that might help you out:

tmp_Data:

LOAD @1 as Data

From [.\Document1.txt]

(txt, codepage is 1252, no labels, delimiter is '\t', msq, filters(Transpose()));

FOR i=0 to (NoOfRows('tmp_Data')/2)-1

   Data:

   LOAD Peek('Data', ($(i)*2)+1, 'tmp_Data') as Hierarchy_UIC

      , Peek('Data', $(i)*2, 'tmp_Data') as Hierarchy_UIC_Label

   AutoGenerate(1);

NEXT i

LET i=Null();

DROP Table tmp_Data;

Both the input file and an example QVW file are included for your pleasure.

Regards,

Philippe

[b_garside]

Thanks Philippe!  What does the @1 represent ?   @1 as Data

[pgrenier]

Hello Brian,

This is how QlikView references unnamed columns in files. In order to prepare the example QVW I have built for you, I have created a .txt file (which is also attached), in which I've copied over the data you provided.

Cheers,

Philippe

[b_garside]

ok, saw both attachments.

So this allows it to not to have each individual field listed?  I have more fields in table but they don't need to be unwrapped beyond the first Six.

[b_garside]

Get this error,

Field not found - <@1>

tmp_Data:

LOAD @1 as Data

FROM

[_Application Data\Service Hierarchy.xlsx]

(ooxml, embedded labels, table is Page1_1, filters(Transpose()))

[pgrenier]

I wasn't sure whether the hierarchy was fixed to 6 levels or not, this is why the TO expression is

(NoOfRows('tmp_Data')/2)-1

Which for 12 entries (6 double entries) returns the value 5.

If you already know you want to stop at 12 entries max, then you can hardcode the valiue directly rather than use the calculated expression.

Cheers,

Philippe

[b_garside]

ok, made change to 12, but I still get this error about the fields not being found whether I use 1@ or the actual field names. ?

[pgrenier]

Hello Brian,

Would you like to attach an example .xslx file to this post (by using the advanced editor), I'll help you out with the column naming.

Cheers,

Philippe