topic Re: Load Dynamic Headers from Excel in QlikView

Load Dynamic Headers from Excel

ciaran_mcgowan — Mon, 17 Jul 2017 08:33:18 GMT

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:

Date	Header 1	Header 2	Header 3	Header 4	Person 1	Person 2	Person 3

Table 2:

Date	Header 1	Header 2	Header 3	Header 4	Person 4	Person 5	Person 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

Re: Load Dynamic Headers from Excel

effinty2112 — Mon, 17 Jul 2017 08:48:16 GMT

Hi,

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

Date	Header 1	Header 2	Header 3	Header 4	Person 1	Person 2	Person 3
a	b	c	d	e	Curly	Larry	Moe

You want to get to:

Date	Header 1	Header 2	Header 3	Header 4	Person
a	b	c	d	e	Curly
a	b	c	d	e	Larry
a	b	c	d	e	Moe

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

Re: Load Dynamic Headers from Excel

ciaran_mcgowan — Mon, 17 Jul 2017 08:55:37 GMT

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.

Re: Load Dynamic Headers from Excel

Gysbert_Wassenaar — Mon, 17 Jul 2017 09:15:31 GMT

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

Re: Load Dynamic Headers from Excel

avinashelite — Mon, 17 Jul 2017 09:26:05 GMT

check this

Dynamically Loading Multiple Excel Files

Re: Load Dynamic Headers from Excel

effinty2112 — Mon, 17 Jul 2017 09:26:35 GMT

Hi Ciaran,

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

Date	Header 1	Header 2	Header 3	Header 4	Person 1	Person 2	Person 3
a	b	c	d	e	Curly	Larry	Moe
sd	w	af	g	af	Curly	Larry	Moe
qrwe	a	gw	e	g	Curly	Larry	Moe
g	af	q	w	er	Curly	Larry	Moe
qrwe	rqw	rt	qwr	w	Curly	Larry	Moe
Date	Header 1	Header 2	Header 3	Header 4	Person 4	Person 5	Person 6
qrwe	rqw	rt	qwr	w	Geddy	Alex	Neil
dvz	sf	sfsf	sf	fsfd	Geddy	Alex	Neil
qrwe	a	gw	e	g	Geddy	Alex	Neil
g	af	q	w	er	Geddy	Alex	Neil
sd	w	af	g	af	Geddy	Alex	Neil
etc.

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

Date	Header 1	Header 2	Header 3	Header 4	Person 1	Person 2	Person 3
a	b	c	d	e	Curly	Larry	Moe
sd	w	af	g	af	Curly	Larry	Moe
qrwe	a	gw	e	g	Curly	Larry	Moe
g	af	q	w	er	Curly	Larry	Moe
qrwe	rqw	rt	qwr	w	Curly	Larry	Moe
qrwe	rqw	rt	qwr	w	Geddy	Alex	Neil
dvz	sf	sfsf	sf	fsfd	Geddy	Alex	Neil
qrwe	a	gw	e	g	Geddy	Alex	Neil
g	af	q	w	er	Geddy	Alex	Neil
sd	w	af	g	af	Geddy	Alex	Neil

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

Re: Load Dynamic Headers from Excel

ciaran_mcgowan — Mon, 17 Jul 2017 09:41:04 GMT

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?

Re: Load Dynamic Headers from Excel

ciaran_mcgowan — Mon, 17 Jul 2017 09:57:57 GMT

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?

Re: Load Dynamic Headers from Excel

effinty2112 — Mon, 17 Jul 2017 10:03:41 GMT

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:

don't forget to click Add:

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:

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

Re: Load Dynamic Headers from Excel

ciaran_mcgowan — Mon, 17 Jul 2017 10:52:02 GMT

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.

Re: Load Dynamic Headers from Excel

ciaran_mcgowan — Mon, 17 Jul 2017 11:02:57 GMT

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.