
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Unwrap table with six column pairs....?
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
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Philippe! What does the @1 represent ? @1 as Data


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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()))


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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. ?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
