topic Loading spreadsheets with different formatting in QlikView

Loading spreadsheets with different formatting

Thu, 25 Jul 2013 17:50:59 GMT

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

Re: Loading spreadsheets with different formatting

marcus_sommer — Sat, 27 Jul 2013 19:52:45 GMT

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

Re: Loading spreadsheets with different formatting

Mon, 29 Jul 2013 14:22:51 GMT

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

Re: Loading spreadsheets with different formatting

marcus_sommer — Mon, 29 Jul 2013 15:03:52 GMT

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

Re: Loading spreadsheets with different formatting

Mon, 29 Jul 2013 15:29:44 GMT

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

Re: Loading spreadsheets with different formatting

marcus_sommer — Tue, 30 Jul 2013 07:23:00 GMT

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

Re: Loading spreadsheets with different formatting

Tue, 30 Jul 2013 12:00:48 GMT

Will do Marcus.

Thanks.

Steve

Re: Loading spreadsheets with different formatting

Tue, 30 Jul 2013 13:09:01 GMT

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

Re: Loading spreadsheets with different formatting

marcus_sommer — Tue, 30 Jul 2013 15:33:41 GMT

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

Re: Loading spreadsheets with different formatting

Tue, 30 Jul 2013 16:12:34 GMT

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

Re: Loading spreadsheets with different formatting

Thu, 01 Aug 2013 16:48:53 GMT

Hi again,

Quick question. I am applying this code to another spreadsheet that is very similar in structure but it is based upon date rather than time interval.

This is the modified code:

calls1:

LOAD

Date,

rowno() as rowno_origin,

if(Date = 'Agent Name', F8,

if(Date <> 'Agent Name', peek(, -1))) as ,

if(Date = 'Agent Name', F19,

if(Date <> 'Agent Name', peek(, -1))) as ,

if(Date = 'Agent Name' or Date = 'Agent Total' or IsNull(Date), 0, 1) as filter

FROM

(biff, embedded labels, header is 7 lines, table is );

calls2:

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

drop table calls1;

It loads fine and for the most part everything looks good but for one thing. The date column info is not right. The date column loads like this:

How do I tweak this code to straighten out the date column? On the previous doc, the interval column worked fine.

I have attached the source document.

Thoughts?

Steve

Re: Loading spreadsheets with different formatting

Thu, 01 Aug 2013 17:11:06 GMT

I got it…It was a problem in the QV presentation and not the code. Sorry to trouble you over nothing.

Steve

From: Steve Connelly

Sent: Thursday, August 01, 2013 12:49 PM

To: 'jive-34751097-2a03-2-80n6@community.qlik.com'

Subject: RE: - Re: Loading spreadsheets with different formatting

Hi again,

Quick question. I am applying this code to another spreadsheet that is very similar in structure but it is based upon date rather than time interval.

This is the modified code:

calls1:

LOAD

Date,

rowno() as rowno_origin,

if(Date = 'Agent Name', F8,

if(Date <> 'Agent Name', peek(, -1))) as ,

if(Date = 'Agent Name', F19,

if(Date <> 'Agent Name', peek(, -1))) as ,

if(Date = 'Agent Name' or Date = 'Agent Total' or IsNull(Date), 0, 1) as filter

FROM

(biff, embedded labels, header is 7 lines, table is );

calls2:

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

drop table calls1;

It loads fine and for the most part everything looks good but for one thing. The date column info is not right. The date column loads like this:

How do I tweak this code to straighten out the date column? On the previous doc, the interval column worked fine.

I have attached the source document.

Thoughts?

Steve

Re: Loading spreadsheets with different formatting

marcus_sommer — Fri, 02 Aug 2013 07:41:17 GMT

It looks good - a date is a numeric value. You could it formatted how do you want in script or GUI, perhaps so (script):

date(Date, 'YYYY-MM-DD') as Date

- Marcus

Re: Loading spreadsheets with different formatting

Fri, 02 Aug 2013 11:54:55 GMT

Thanks! I will.

Have a great weekend.

Steve