Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
b_garside
Valued Contributor

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 01TOE_NAME_LEV_1UIC Level 02TOE_NAME_LEV_2UIC Level 03TOE_NAME_LEV_3UIC Level 04TOE_NAME_LEV_4UIC Level 05TOE_NAME_LEV_5UIC Level 06TOE_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

Tags (1)
1 Solution

Accepted Solutions
pgrenier
Contributor III

Re: Unwrap table with six column pairs....?

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

8 Replies
pgrenier
Contributor III

Re: Unwrap table with six column pairs....?

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
Valued Contributor

Re: Unwrap table with six column pairs....?

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

pgrenier
Contributor III

Re: Unwrap table with six column pairs....?

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
Valued Contributor

Re: Unwrap table with six column pairs....?

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
Valued Contributor

Re: Unwrap table with six column pairs....?

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
Contributor III

Re: Unwrap table with six column pairs....?

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
Valued Contributor

Re: Unwrap table with six column pairs....?

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
Contributor III

Re: Unwrap table with six column pairs....?

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

Community Browser