Loading spreadsheets with different formatting

[#1]

Good afternoon all,

I am working spreadsheets that are exported from our phone system to create a new QV doc. The spreadsheets are not broken out in what I refer to as a traditional format, they have large headers (I know how to get around that) but they then have the report broken up in secyons for each person like so:

I select the number of lines in the header to get past that and select the embedded labels (yellow highlight) etc but how do I work with the fact that the employees are not columnar. the rows of data are grouped by employee (blue highligted rows). I don't know exactly how to make sense of that in the loading script or QV itself.

How do I separate out the rows where each section is totaled and the rows that determine which employee the next group of data belongs to?

Any help out there on this one?

Thanks in advance,

Steve

[marcus_sommer]

Try this: (perhaps you must change the columnames)

calls1:

Load

*,

if([Interval Starting at] = 'Agent Name', 'avg ACD ring time',

     if(peek('Interval Starting at', - 1) <> 'Agent Name', peek('Agent Name', -1))) as [Agent Name],

if([Interval Starting at] = 'Agent Name' or [Interval Starting at] = 'Agent Total', 0, 1) as filter

From phonecalls ....

calls2:

Load * Resident calls1 Where filter = 1;

- Marcus

[#3]

Hi Marcus, thanks for the help!

I am not quite sure how to apply this.

Here is what I have done:

Directory;
LOAD [Interval starting at],
     [ACD answered calls],
     [Avg ACD treatment time],
     F8 as [Agent Name],
     [Avg ACD hold time],
     [Avg ACD ring time],
     [Avg ring time of ACD abandoned],
     [NACD incoming external calls],
     [NACD incoming internal calls],
     [NACD outgoing calls],
     [NACD outgoing external calls],
     [NACD outgoing internal calls],
     [Total calls]
FROM
[..\..\Desktop\Jim Strickland\3.1 Agent By Interval.XLS]
(biff, embedded labels, header is 7 lines, table is [Sheet1$]);

calls1:
LOAD
*,
IF([Interval Starting at] = 'Agent Name', 'avg ACD ring time',
     IF(PEEK('Interval Starting at', - 1) <> 'Agent Name', PEEK('Agent Name', -1))) AS [Agent Name],
IF([Interval Starting at] = 'Agent Name' OR [Interval Starting at] = 'Agent Total', 0, 1) AS filter
FROM
[..\..\Desktop\Jim Strickland\3.1 Agent By Interval.XLS]
(biff, embedded labels, header is 7 lines, table is [Sheet1$]);

calls2:
LOAD*Resident calls1 Where filter = 1;

But this throws the following error:

So obviously i am doing something wrong here.

Thoughts?

Steve

[marcus_sommer]

Try this (small changes from single quotes to brakets / the first load with directory will don't needed):

calls1:

Load

*,

if([Interval Starting at] = 'Agent Name', [avg ACD ring time],

     if(peek([Interval Starting at], - 1) <> 'Agent Name', peek([Agent Name], -1))) as [Agent Name],

if([Interval Starting at] = 'Agent Name' or [Interval Starting at] = 'Agent Total', 0, 1) as filter

From phonecalls ....

calls2:

Load * Resident calls1 Where filter = 1;

- Marcus

[#5]

Hi Marcus,

I am still working with your first bit of code. I got it cleaned up so it would run:

calls1:

Load

[Interval starting at],

     [ACD answered calls],

     F5,

     [Avg ACD treatment time],

     F8,

     [Avg ACD hold time],

     [Avg ACD ring time],

     [Avg ring time of ACD abandoned],

     F17,

     [NACD incoming external calls],

     [NACD incoming internal calls],

     [NACD outgoing calls],

     [NACD outgoing external calls],

     [NACD outgoing internal calls],

     [Total calls],

if([Interval starting at] = 'Agent Name', 'avg ACD ring time',

     if(peek('Interval Starting at', - 1) <> 'Agent Name', peek('Agent Name', -1))) as [Agent Name],

if([Interval starting at] = 'Agent Name' or [Interval starting at] = 'Agent Total', 0, 1) as filter

FROM

[..\..\Desktop\Jim Strickland\3.1 Agent By Interval.XLS]

(biff, embedded labels, header is 7 lines, table is [Sheet1$]);

calls2:

Load * Resident calls1 Where filter = 1;

(needed a lower case "s" in "Interval Starting at"...that hung me up for a while.

It runs now but the result is not what i am looking for:

No one thing, I can pull the totals rows our of the spreadsheet. i can work totals on QV. The one thing I need is the agent breakout.

I have not gotten your new code to run yet.

Steve

[marcus_sommer]

Generally the way I suggest will work, perhaps it are not the right columns choosen and/or little mistakes in syntax/logic - but in the screeshots it isn't really good to see. Try it again with a few little changes and it is don't work upload here a small copy from your spreadsheet.

- Marcus

[#7]

Will do Marcus.

Thanks.

Steve

[#8]

OK, I have worked with this and I think that maybe I was not clear what I needed.

Here is the code I am using:

I have attached three spreadsheets. One is the original sheet I am loading into QV. One is the actual results generated by the above code and one is more like what I need this to look like.

calls1:
Load [Interval starting at],
     [ACD answered calls],
     F5,
     [Avg ACD treatment time],
     F8,
     [Avg ACD hold time],
     [Avg ACD ring time],
     [Avg ring time of ACD abandoned],
     F17,
     [NACD incoming external calls],
     [NACD incoming internal calls],
     [NACD outgoing calls],
     [NACD outgoing external calls],
     [NACD outgoing internal calls],
     [Total calls],

if([Interval starting at] = 'Agent Name', F8,

     if(peek([Interval starting at], - 1) <> 'Agent Name', peek('Agent Name', -1))) as [Agent Name],

if([Interval starting at] = 'Agent Name' or [Interval starting at] = 'Agent Total', 0, 1) as filter

FROM
[..\..\Desktop\Jim Strickland\3.1 Agent By Interval.XLS]
(biff, embedded labels, header is 7 lines, table is [Sheet1$]);

calls2:
Load * Resident calls1 Where filter = 1;

Let me know if this makes more sense.

NOTE: I really don’t care about totals or agent number. I just want to have a column where I can see which agent applies to each row in the spreadsheet.

Thanks,

Steve

[marcus_sommer · Accepted solution]

This worked - see also attachment.

calls1:

LOAD

    [Interval starting at],

    [ACD answered calls],

    [Avg ACD treatment time],

    [Avg ACD hold time],

    [Avg ACD ring time],

    [Avg ring time of ACD abandoned],

    [NACD incoming external calls],

    [NACD incoming internal calls],

    [NACD outgoing calls],

    [NACD outgoing external calls],

    [NACD outgoing internal calls],

    [Total calls],

    rowno() as rowno_origin,

    if([Interval starting at] = 'Agent Name', F8,

        if([Interval starting at] <> 'Agent Name', peek([Agent Name], -1))) as [Agent Name],

    if([Interval starting at] = 'Agent Name', F17,

        if([Interval starting at] <> 'Agent Name', peek([Agent Number], -1))) as [Agent Number],

    if([Interval starting at] = 'Agent Name' or [Interval starting at] = 'Agent Total' or IsNull([Interval starting at]), 0, 1) as filter

FROM [3.1 Agent By Interval.xls] (biff, embedded labels, header is 7 lines, table is Sheet1$);

calls2:

NoConcatenate Load *, rowno() as rowno_target Resident calls1 Where filter = 1;

drop table calls1;

- Marcus

[#10]

Marcus, you are awesome!

This worked just as I needed. And I can see how you did it so I can leverage this for other applications and increase my understanding.

Thanks a million!

Steve