Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
ciaran_mcgowan
Partner - Creator III
Partner - Creator III

Load Dynamic Headers from Excel

Hi all,

Can anyone share a good method for loading multiple tables from the same excel tab into QV? Each table has a similar structure except the amount of rows can vary (between 9 and 15) and the last 3 columns have different headers.

Table 1:

DateHeader 1Header 2Header 3Header 4Person 1Person 2Person 3

Table 2:

DateHeader 1Header 2Header 3Header 4Person 4Person 5Person 6

The tab can contain anywhere between 10 & 30 tables and as I mentioned, the values in each cell are the same format, only the header names.

Thanks.

Update: Apologies if my requirements were too vague, here is a little more detail:

Headers 1-4 fields contain picklists relating to each person.

Person (n) contains numeric scores and their name is the header.

The sheet has more than 2 tables (unknown amount)

Message was edited by: Ciarán McGowan

1 Solution

Accepted Solutions
ciaran_mcgowan
Partner - Creator III
Partner - Creator III
Author

Hi Gysbery,

Thanks for pointing me in the right direction. I am loading all files in the excel sheet and then using the following code to store each header name as a field:

  if(Peek(Header) = 'Date',
 
Peek(Person1),
  
Peek(Person)) AS Person


This fills creates a Person field for everyone in the Person1 column and fills in the previous row's data. I repeat & concatenate for the Person2 & Person3 columns. Then drop blank rows between tables and all header rows, manually naming each column.

View solution in original post

10 Replies
effinty2112
Master
Master

Hi,

Would it be right to say that for every record like:

DateHeader 1Header 2Header 3Header 4Person 1Person 2Person 3
abcdeCurlyLarryMoe

You want to get to:

DateHeader 1Header 2Header 3Header 4Person
abcdeCurly
abcdeLarry
abcdeMoe

?

If so then I would load the entire sheet and use the File Wizard transformation step and follow a strategy something like:

Delete all header rows apart from the first one.

Crosstable with Date and Header 1,2,3 & 4 as Qualifier Fields

Call the Attribute Field Person

Call the Data Field something appropriate to the measure.

Good luck

Andrew

ciaran_mcgowan
Partner - Creator III
Partner - Creator III
Author

Hi Andrew,

That's what I was thinking but running into difficulties when I get to the second table, where the people are different. I would need a solution that caters for multiple cross tables.

Gysbert_Wassenaar

You can force concatenation so the tables are concatenated into one table. In that case you'd get different fields for the last three columns of each excel table. So you'd have person1 through person6 in the final table.

Or you can name the fields yourself and don't use the column headers as field names:

MyTable:

LOAD @1 as Date, @2 as Header1, .... @6 as A, @7 as B, @8 as C

FROM abc.xlsx (ooxml, no labels, header is 1 lines).


talk is cheap, supply exceeds demand
avinashelite

effinty2112
Master
Master

Hi Ciaran,

I would load the entire spreadsheet as the first step , all tables, like

 

DateHeader 1Header 2Header 3Header 4Person 1Person 2Person 3
abcdeCurlyLarryMoe
sdwafgafCurlyLarryMoe
qrweagwegCurlyLarryMoe
gafqwerCurlyLarryMoe
qrwerqwrtqwrwCurlyLarryMoe
DateHeader 1Header 2Header 3Header 4Person 4Person 5Person 6
qrwerqwrtqwrwGeddyAlexNeil
dvzsfsfsfsffsfdGeddyAlexNeil
qrweagwegGeddyAlexNeil
gafqwerGeddyAlexNeil
sdwafgafGeddyAlex

Neil

etc.

Then delete all rows where column 1 equals 'Date', starting from row 2:

 

DateHeader 1Header 2Header 3Header 4Person 1Person 2Person 3
abcdeCurlyLarryMoe
sdwafgafCurlyLarryMoe
qrweagwegCurlyLarryMoe
gafqwerCurlyLarryMoe
qrwerqwrtqwrwCurlyLarryMoe
qrwerqwrtqwrwGeddyAlexNeil
dvzsfsfsfsffsfdGeddyAlexNeil
qrweagwegGeddyAlexNeil
gafqwerGeddyAlexNeil
sdwafgafGeddyAlexNeil

Then carry on with the crosstable part. The file wizard transformation may be a bit awkward to use at first but when you get into the way of using it's very powerful.

cheers

Andrew

ciaran_mcgowan
Partner - Creator III
Partner - Creator III
Author

Thanks again for your reply,

If I load all tables into one and delete all header rows after the first one, then won't I lose the names of all people who aren't in the first table?

ciaran_mcgowan
Partner - Creator III
Partner - Creator III
Author

Hi Gysbert,

I don't know concatenation could work here as there is an unknown number of tables, each with different People (I've updated my requirements to make it more clear).

Unless there was a way to move each Person (n) header into a row?

effinty2112
Master
Master

Hi Ciaran

No you won't. The header lines are loaded just as text. The first one can be used to create the headers names in your QV table (embedded labels) and the rest can be deleted. Use the file wizard. Hit the conditional Delete button and enter the condition shown:

cond.jpg

don't forget to click Add:

cond1.jpg

click OK Edit

Add the additional condition that we do this from the second row to the last.

Select the Range button. Click From and enter 2 from the top. Now click To and enter 1 from the bottom. click Select and just press OK here.

don't forget to click Add:


cond2.jpg

Click OK. As you add transformations you should see the effect on the screen. In a simiar way you can delete blank lines or any other garbage too.


Good luck


Andrew

ciaran_mcgowan
Partner - Creator III
Partner - Creator III
Author

Thanks again for your help but I'm just not that savvy with the Transformation Wizard. You've shown me that I need to study it more.

For now, I have found the solution I was looking for, which I'll post shortly.