topic Apply group header to rows. in QlikView

Apply group header to rows.

Tue, 02 Jun 2015 12:51:48 GMT

Need to fetch the names in blue and apply them to each row in a new column.

Any ideas?

RAW DATA

DESIRED RESULT IN QlikVieW

Re: Apply group header to rows.

marcus_sommer — Tue, 02 Jun 2015 13:13:13 GMT

With peek() or previous() you could grab previous rows - see also: Peek() vs Previous() – When to Use Each

and just written down without testing:

Load * where len(Side) >= 1;

Load *, if(len(peek('Side') = 0, peek('BalancePool')) as Name From RawData;

- Marcus

Re: Apply group header to rows.

jonathandienst — Tue, 02 Jun 2015 13:17:16 GMT

Looks a good start - you may also need this:

Load * where len(Side) >= 1;

Load *, if(len(peek(Side) = 0, peek(BalancePool), peek(Name)) as Name From RawData;

to populate Name if there is more than one row under one heading.

Re: Apply group header to rows.

Tue, 02 Jun 2015 13:42:36 GMT

Looks promising!

Im a bit of a nube, so how would you implement this?

Something like the below?

Also, can you see what might be wrong with the IF-statement?

Directory;
LOAD BalancePool,
    Side,
    [Balance Date],
    [Available Balance],
    [Avail Bal DCIP],
    [Booked Balance]
FROM
testBalances.xls
(biff, embedded labels, table is [(020)Balance Report$]);

Load * where len(Side) >= 1;
Load *, if(len(peek(Side) = 0, peek(BalancePool), peek(Name)) as Name;

Re: Apply group header to rows.

Thu, 04 Jun 2015 06:50:32 GMT

Can you pls give me a QV-file example?

Would make my day!

Re: Apply group header to rows.

MayilVahanan — Thu, 04 Jun 2015 07:11:49 GMT

Try like this

TestBalances:

Load *, if(len(peek('Side')) = 0, peek('BalancePool'), Peek('Name')) as Name;

LOAD BalancePool,

Side,

[Balance Date],

[Available Balance],

[Avail Bal DCIP],

[Booked Balance]

FROM

testBalances.xls

(biff, embedded labels, table is [(020)Balance Report$]);

NoConcatenate

LOAD * Resident TestBalances Where IsNum(BalancePool);

DROP Table TestBalances;