topic Re: Looping through the database for repeating headers in QlikView

Looping through the database for repeating headers

sunny_talwar — Mon, 20 Oct 2014 17:20:05 GMT

Hi there community experts,

I am trying to upload from a excel database which is not like the other database I have dealt with, so I need some expert advice. The database looks something like this:

This is what I am trying to do:

This is what I tried to do thus far with the scripting:

Agent:

LOAD * INLINE [

Agent

Agent1

Agent2

Agent3

];

AgentCount:

LOAD Count(DISTINCT Agent) as AgentCount

Resident Agent;

LET vAgentCount = Peek('AgentCount', 0, 'AgentCount') - 1;

AgentTable:

LOAD [Record No.],

abc,

def,

ghi,

Peek('Agent', 0, 'Agent') as AgentName

FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

For i = 1 to vAgentCount

Concatenate(AgentTable)

LOAD [Record No.],

abc,

def,

ghi,

Peek('Agent', $(i), 'Agent') as AgentName

FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

The problem is QV reads the repeating headers as following;

Agent 1 :abc, def, ghi

Agent 2: abc1, def1, ghi1

Agent 3: abc2, def2, ghi2

What would be a workaround or is there another (better) way to get this done?

Note: Number of Agent's may increase and that is why I thought of using For loop.

Thanks in advance for any help provided,

Updating the output image because the one right now might be a little confusing as I still have Agent 1 at the top. The new output image file is attached below:

Message was edited by: Sunny T

Re: Looping through the database for repeating headers

JonnyPoole — Mon, 20 Oct 2014 17:22:38 GMT

I suggest using a CROSSTABLE load instead of a standard load.

CROSSTABLE allows you to turn field headers into 1 field with different data values (like a pivot). The oposite of a crosstable load is the generic load which turns unique values in one field into multiple fields.

Re: Looping through the database for repeating headers

sunny_talwar — Mon, 20 Oct 2014 17:25:52 GMT

Thanks for a very quick response.

If I attach my example in a qvw file, would u be able to show me how it would work?

Best,

Re: Re: Looping through the database for repeating headers

sunny_talwar — Mon, 20 Oct 2014 17:32:07 GMT

PFA the qwv and the accompanying Excel file.

Best,

Re: Looping through the database for repeating headers

sunny_talwar — Tue, 21 Oct 2014 13:15:46 GMT

Really! Nobody has dealt with a similar problem before? Kindly help if anybody have dealt with something like this before.

Best,