Discussion Board for collaboration on QlikView Scripting.
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;
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)) )) ));
Go to Solution.
Here is a code snippet that might help you out:
LOAD @1 as Data
(txt, codepage is 1252, no labels, delimiter is '\t', msq, filters(Transpose()));
FOR i=0 to (NoOfRows('tmp_Data')/2)-1
LOAD Peek('Data', ($(i)*2)+1, 'tmp_Data') as Hierarchy_UIC
, Peek('Data', $(i)*2, 'tmp_Data') as Hierarchy_UIC_Label
DROP Table tmp_Data;
Both the input file and an example QVW file are included for your pleasure.
Thanks Philippe! What does the @1 represent ? @1 as Data
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.
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.
Get this error,
Field not found - <@1>
[_Application Data\Service Hierarchy.xlsx]
(ooxml, embedded labels, table is Page1_1, filters(Transpose()))
I wasn't sure whether the hierarchy was fixed to 6 levels or not, this is why the TO expression is
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.
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. ?
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.