Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
PFA the qwv and the accompanying Excel file.
Best,
S
Really! Nobody has dealt with a similar problem before? Kindly help if anybody have dealt with something like this before.
Best,
S