2 lines of header..?

[#1]

HI all,

This may be a really silly question but nver had this before. We want to import the following. There is 2 lines of headers (see bold) which we need to use a list boxes etc. However, only ever imported one row of headers and can't seem to see how to change this?

Can anyone help?

What is the   postcode that you travel from?

What time do   you get into work on average?

What time do   you leave work on average?

How far is your journey to work?

Which company   site do you mostly work at?

How often do   you travel to your place of work in the ways below (in a standard week)?

Response

Other (please specify time)

Response

Other (please specify time)

Response

Response

Other (please specify)

Walk

Car alone

Car share (as a passenger)

Car share (as a driver)

Cycle

Bus

Train

Tram

Motorcycle

Taxi

M30 0jN

8:30 - 9:00am

5:00 - 5:30pm

1-2 miles

Centenary House

Every day

Never

Never

Never

Never

Never

Never

Never

Never

Never

SK8 5PL

8:30 - 9:00am

5:00 - 5:30pm

11-19 miles

Centenary House

Never

Every day

Never

Never

Never

Never

Never

Never

Never

Never

[Michiel_QV_Fan]

You cannot import different lines as 1 header.

You need to change your source or perform some kind of combined load to join the headers.

Import without the headers and rename the columns in the script might be an option, if the headers do not change much over time.

[luis_pimentel]

I had the same problem a few days ago, and I managed to solved in a tricky way:

1º Load all data without header and create a new RecNo() field.

2º In a second load, load just the headers with something like:

     LEFT JOIN (yourprevioustable)

     LOAD

     RecNo() as Recno,

     IF(F2='' AND F1 = PEEK('F1',-1) AND PEEK('F2',-1)<>'', F1 & ' - ' & PEEK('F2',-1), IF(F2<>'', F1 & ' - ' & F2, F1 )    )AS HEADER,

     F1,

     F2

     Resident yourfile.xls ....

Like I said, It's tricky, but It works for me.

Hope this can help

-Luis.

[rbecher]

Hi Chris,

it is possible with some scripting: Combine two lines into one label from load (e.g. excel)

Could you please uploade a sample file and the expected result. Then I can build a load script..

- Ralf

[rbecher]

I think this is really hard to solve because the structure is inconsistent, too.. But possible.

[rbecher]

This could be a solution. But it's a guess because I don't really know what you will expect:

Set vQuestionField = 8;

Header:
FIRST 2 LOAD * FROM
[TRAVEL SURVEY EXAMPLE.xlsx]
(ooxml, no labels, table is Sheet1);

Let vNoOfFields = NoOfFields('Header');

FOR i=1 to $(vNoOfFields)
    Let vFieldOld = FieldName($(i), 'Header');

    IF $(i) >= $(vQuestionField) THEN
        IF Not IsNull(Peek('$(vFieldOld)', 0, 'Header')) THEN
            Let vFieldGroup = Peek('$(vFieldOld)', 0, 'Header');
        ENDIF        
        Let vFieldNew = '$(vFieldGroup)' & ' / ' & Peek('$(vFieldOld)', 1, 'Header');        
    ELSE
        Let vFieldNew = Peek('$(vFieldOld)', 0, 'Header');
    ENDIF

    HeaderMap:
    LOAD '$(vFieldOld)' as FieldOld, '$(vFieldNew)' as FieldNew AutoGenerate(1);
NEXT

STORE HeaderMap INTO HeaderMap.qvd (qvd);

Drop Tables Header, HeaderMap;

Results:
LOAD * FROM
[TRAVEL SURVEY EXAMPLE.xlsx]
(ooxml, no labels, table is Sheet1)
WHERE RecNo()>2;

HeaderMap:
MAPPING LOAD * FROM HeaderMap.qvd (qvd);

RENAME FIELDS USING HeaderMap;

- Ralf

[#7]

Hi Ralf,

This looks great. The spreadsheet I attached as a test just had one record. If we want to create what you have done but to capture remaining records is it a simple change in the script (if so please advise) or something more advanced?

Chris

[rbecher]

Hi Chris,

this should work if the Excel sheet has 2 header lines following by several record lines.

- Ralf

[rbecher]

Just change the file name in the last LOAD:

Results:

LOAD * FROM

[travel survey full.xlsx]

(ooxml, no labels, table is Sheet1)

WHERE RecNo()>2;

----

Btw. UI is another story..

[rbecher]

No, better in all LOADs, of course 😉