Looping through the database for repeating headers

[sunny_talwar]

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,

S

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

[JonnyPoole]

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.

[sunny_talwar]

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,

S

[sunny_talwar]

PFA the qwv and the accompanying Excel file.

Best,

S

[sunny_talwar]

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

Best,

S