topic Unwrap table with six column pairs....? in QlikView

Unwrap table with six column pairs....?

b_garside — Mon, 16 Sep 2013 18:18:54 GMT

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

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

pgrenier — Mon, 16 Sep 2013 23:44:00 GMT

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

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

b_garside — Tue, 17 Sep 2013 15:24:46 GMT

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

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

pgrenier — Tue, 17 Sep 2013 15:29:12 GMT

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

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

b_garside — Tue, 17 Sep 2013 15:38:31 GMT

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.

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

b_garside — Tue, 17 Sep 2013 15:43:58 GMT

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()))

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

pgrenier — Tue, 17 Sep 2013 15:44:33 GMT

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

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

b_garside — Tue, 17 Sep 2013 15:51:54 GMT

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. ?

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

pgrenier — Tue, 17 Sep 2013 17:52:55 GMT

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