Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
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
Luminary Alumni
Luminary Alumni

Please find attached

Vizlib Head of R&D

View solution in original post

13 Replies
michielvandegoo
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
Partner

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
Luminary Alumni
Luminary Alumni

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

Vizlib Head of R&D
rbecher
Luminary Alumni
Luminary Alumni

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

Vizlib Head of R&D
rbecher
Luminary Alumni
Luminary Alumni

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

Vizlib 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
Luminary Alumni
Luminary Alumni

Hi Chris,

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

- Ralf

Vizlib Head of R&D
rbecher
Luminary Alumni
Luminary Alumni

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

Vizlib Head of R&D
rbecher
Luminary Alumni
Luminary Alumni

No, better in all LOADs, of course 😉

Vizlib Head of R&D