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

[#2]

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

[#3]

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

[marcus_sommer]

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

[#5]

Thanks! I will.

Have a great weekend.

Steve