Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

2 lines of header..?

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)?
ResponseOther (please specify time)ResponseOther (please specify time)ResponseResponseOther (please specify)WalkCar aloneCar share (as a passenger)Car share (as a driver)CycleBusTrainTramMotorcycleTaxi
M30 0jN8:30 - 9:00am5:00 - 5:30pm1-2 milesCentenary HouseEvery dayNeverNeverNeverNeverNeverNeverNeverNeverNever
SK8 5PL8:30 - 9:00am5:00 - 5:30pm11-19 milesCentenary HouseNeverEvery dayNeverNeverNeverNeverNeverNeverNeverNever
1 Solution

Accepted Solutions
rbecher
MVP
MVP

Please find attached

Astrato.io Head of R&D

View solution in original post

13 Replies
Michiel_QV_Fan
Specialist
Specialist

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
Partner - Creator III
Partner - Creator III

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
MVP
MVP

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

Astrato.io Head of R&D
rbecher
MVP
MVP

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

Astrato.io Head of R&D
rbecher
MVP
MVP

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

Astrato.io Head of R&D
Not applicable
Author

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
MVP
MVP

Hi Chris,

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

- Ralf

Astrato.io Head of R&D
rbecher
MVP
MVP

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..

Astrato.io Head of R&D
rbecher
MVP
MVP

No, better in all LOADs, of course 😉

Astrato.io Head of R&D