topic Re: Re: conditional unwrap during load in QlikView

conditional unwrap during load

Wed, 30 Oct 2013 10:14:16 GMT

Hello,

does anyone know of a possibility to use conditional unwrapping during the transformation step?

I can only specify ROW conditions but need to be able to specify column conditions.

Right now i do it manually and not "automated":

[....]

Unwrap(Col, Pos(Top, 6)),

[....]

What i need to do is unwrap after every 6th column if there is still data in following column.

Is there any way to do this?

My table looks like this:

a	b	c	a	b	c	a
32	123	45	321	321	2213	312

What i hope to get is:

a	b	c
32	123	45
321	321	2213

.... and so on.

Thank you!

Re: conditional unwrap during load

Gysbert_Wassenaar — Wed, 30 Oct 2013 10:38:16 GMT

Perhaps a transpose is enough to get the result you want. See attached example

Re: Re: conditional unwrap during load

Wed, 30 Oct 2013 12:08:57 GMT

hmm i don't really get it.. i mean the result of your example is correct but i don't know how you get there..

My original table is already transposed to get the structure i provided.

right now it looks like this:

DATALOAD2_TXT:
LOAD left(filename(), index(filename(), '_')-1) as SN,
[Workstep:],
[Operation:],
[Start:],
[Stop:],
[Duration:]
FROM
[$(vFileName)] (txt, codepage is 1252, embedded labels, delimiter is spaces, msq, header is 1 lines, filters(
Transpose(),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Unwrap(Col, Pos(Top, 6)),
Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),
Remove(Row, RowCnd(Compound,
RowCnd(CellValue, 1, StrCnd(equal, 'Workstep:')),
RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 1), Select(1, 0))
))
));

The source file (txt) has the following structure:

Workstep: EM04=HMHD654036_NC=654036_NC=EDM_1

Operation: EM04=006110500002=0004=2

Start: 22.10.13,16:05:23

Stop: 22.10.13,16:12:01

Duration: 000:06:38

Workstep: EM04=HMHD654036_NC=654036_NC=EDM_2

Operation: EM04=006110500002=0004=2

Start: 22.10.13,16:12:20

Stop: 22.10.13,16:22:04

Duration: 000:09:44

and so on.

Re: Re: Re: conditional unwrap during load

Gysbert_Wassenaar — Wed, 30 Oct 2013 12:28:15 GMT

Perhaps like this then:

Table1:

LOAD subfield(@1,': ',1) as Key,

subfield(@1,': ',2) as Value,

ceil(rowno()/5) as Index

FROM [$(vFileName)] (txt, codepage is 1252, no labels, delimiter is '|', msq);

Or maybe you want to a generic load instead. In that case add the keyword GENERIC in front of LOAD in the load statement.

Re: conditional unwrap during load

Wed, 30 Oct 2013 13:02:56 GMT

thank you, but this only displays the data correctly in a pivot chart.

My goal was to get the QV Table in the correct format.

Is there no unwrap solution?

Re: Re: conditional unwrap during load

Gysbert_Wassenaar — Wed, 30 Oct 2013 13:19:25 GMT

Sure. Use a generic load as I said. Or do something like five loads, one for each field and join the lot.

Table2:
load rowno() as row,
subfield(@1,': ',2) as Workstep
FROM
[$(vFileName)]
(txt, codepage is 1252, no labels, delimiter is '|', msq)
where subfield(@1,': ',1) = 'Workstep' ;

join

load rowno() as row,
subfield(@1,': ',2) as Operation
FROM
[$(vFileName)]
(txt, codepage is 1252, no labels, delimiter is '|', msq)
where subfield(@1,': ',1) = 'Operation' ;

join

load rowno() as row,
subfield(@1,': ',2) as Start
FROM
[$(vFileName)]
(txt, codepage is 1252, no labels, delimiter is '|', msq)
where subfield(@1,': ',1) = 'Start' ;

join

load rowno() as row,
subfield(@1,': ',2) as Stop
FROM
[$(vFileName)]
(txt, codepage is 1252, no labels, delimiter is '|', msq)
where subfield(@1,': ',1) = 'Stop' ;

join

load rowno() as row,
subfield(@1,': ',2) as Duration
FROM
[$(vFileName)]
(txt, codepage is 1252, no labels, delimiter is '|', msq)
where subfield(@1,': ',1) = 'Duration' ;

drop field row;

Re: conditional unwrap during load

Wed, 30 Oct 2013 15:29:20 GMT

thank you so much this worked!

could you kindly tell me why "delimiter is '|'" works and "delimiter is spaces" not?...

Re: Re: conditional unwrap during load

Gysbert_Wassenaar — Wed, 30 Oct 2013 15:42:04 GMT

delimiter is spaces will work too. You just need to add the : to the match string in the where clause:

Table2:

load rowno() as row,

@2 as Workstep

FROM

[$(vFileName)]

(txt, codepage is 1252, no labels, delimiter is spaces, msq)

where @1= 'Workstep:' ;