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: 
gshockxcc
Creator
Creator

How to load Multiple field headings

I am fairly new to Qlikview.  I'm sure there's a way to approach this, but I can't figure out how to get started.

First problem is that the data is transposed for how I want it.  Not an issue, because I figured out how to correct it in the Table File wizard using the "Rotate", then selecting "Transpose".

That creates a new problem.  When I load the data, I end up with repeated fields that are appended with a number, i.e. Field, Field1, Field2, etc, because the same field name is repeated more than once.

I also have a Sub-Field below the Field headings.  I would like to make use of that, but I am not sure how.

The data appears to be a Pivot Table result.  If I have good data, I know how to create a Pivot Table, but I don't know how to take the data and go the other way using the load script.

Any help would be greatly appreciated.  Sample data and QVW attached.  Thank you in advance.

1 Solution

Accepted Solutions
evan_kurowski
Specialist
Specialist

  Hello Kristan,

 

Taking a look at your source spreadsheet, your objective to turn this source data into a non-pivoted form seems the optimal way to arrange the QlikView data model.  This will offer the most flexibility in terms of visualizations going forward, without having to worry about un-pivoting data so that it can be re-pivoted in some other way.

It looks like your source spreadsheet ‘CapDemand_Canada.xlsx’ has two sheets

 

Sheet: [Sheet1]

Horizontal dimensions: Date

Vertical dimensions: Resource, Record Type

 

Sheet: [Data]

Horizontal dimensions: Resource, Record Type

Vertical dimensions: Date

Since both these sheets are a transposition of the same data, you could extract your final QlikView table from either of them, but in this example the sheet using the single horizontal dimension can then be parsed using a single pass of the Crosstable() function, and offer the less complicated syntax.

Use the following to load your “vertical” or “non-pivoted” format of this data into QlikView:


[Import_Cross]:
CrossTable(Date, Data, 3)
LOAD *
FROM
(
ooxml, embedded labels, header is 2 lines, table is Sheet1);

//this second pass at the table is only necessary

//if you want to fully populate Resource_ID
//if not you can skip the second load

[FINAL]:
NOCONCATENATE LOAD If(Len(F1)>0,F1, Peek(Resource_ID)) AS Resource_ID,
Resource, [Record Type], Date, Data RESIDENT [Import_Cross];

DROP TABLE [Import_Cross];

 

 

I noticed on the Excel sheet [Sheet1] that Column A is a numeric [Resource ID] and is only populated on the first row of each new [Resource] value.  In my example I keep [Resource ID] in your final table, and populating 100% where entries would have been null. 

Other options would be to load the [Resource ID] into a separate dimension table, connecting to fact table on [Resource], or just dropping the field [Resource ID] altogether if not necessary for visualization.

The output looks like this:

   

 

With this table structure in your QlikView data model, you should be able to reform both of the pivot structures shown in both tabs of your spreadsheet (using QlikView Pivot Tables), while still storing the data in the most versatile non-pivoted form. 

 

View solution in original post

6 Replies
Not applicable

I think I understand what you're trying to do.  Try this

Table:

CrossTable(Date, Data, 2)

LOAD Resource,

     [Record Type],

     [Oct 16 15],

     [Oct 17 15],

     [Oct 18 15],

     [Oct 19 15],

     [Oct 20 15],

     [Oct 21 15],

     [Oct 22 15],

     [Oct 23 15],

     [Oct 24 15],

     [Oct 25 15],

     [Oct 26 15],

     [Oct 27 15],

     [Oct 28 15],

     [Oct 29 15],

     [Oct 30 15],

     [Oct 31 15],

     [Nov 01 15],

     [Nov 02 15],

     [Nov 03 15],

     [Nov 04 15],

     [Nov 05 15],

     [Nov 06 15],

     [Nov 07 15],

     [Nov 08 15],

     [Nov 09 15],

     [Nov 10 15],

     [Nov 11 15],

     [Nov 12 15],

     [Nov 13 15],

     [Nov 14 15],

     [Nov 15 15],

     [Nov 16 15],

     [Nov 17 15],

     [Nov 18 15],

     [Nov 19 15],

     [Nov 20 15],

     [Nov 21 15],

     [Nov 22 15],

     [Nov 23 15],

     [Nov 24 15],

     [Nov 25 15],

     [Nov 26 15],

     [Nov 27 15],

     [Nov 28 15],

     [Nov 29 15],

     [Nov 30 15],

     [Dec 01 15],

     [Dec 02 15],

     [Dec 03 15],

     [Dec 04 15],

     [Dec 05 15],

     [Dec 06 15],

     [Dec 07 15],

     [Dec 08 15],

     [Dec 09 15],

     [Dec 10 15],

     [Dec 11 15],

     [Dec 12 15],

     [Dec 13 15],

     [Dec 14 15],

     [Dec 15 15],

     [Dec 16 15],

     [Dec 17 15],

     [Dec 18 15],

     [Dec 19 15],

     [Dec 20 15],

     [Dec 21 15],

     [Dec 22 15],

     [Dec 23 15],

     [Dec 24 15],

     [Dec 25 15],

     [Dec 26 15],

     [Dec 27 15],

     [Dec 28 15],

     [Dec 29 15],

     [Dec 30 15],

     [Dec 31 15]

FROM

CapDemand_Canada.xlsx

(ooxml, embedded labels, table is Data, filters(

Transpose()

));

evan_kurowski
Specialist
Specialist

  Hello Kristan,

 

Taking a look at your source spreadsheet, your objective to turn this source data into a non-pivoted form seems the optimal way to arrange the QlikView data model.  This will offer the most flexibility in terms of visualizations going forward, without having to worry about un-pivoting data so that it can be re-pivoted in some other way.

It looks like your source spreadsheet ‘CapDemand_Canada.xlsx’ has two sheets

 

Sheet: [Sheet1]

Horizontal dimensions: Date

Vertical dimensions: Resource, Record Type

 

Sheet: [Data]

Horizontal dimensions: Resource, Record Type

Vertical dimensions: Date

Since both these sheets are a transposition of the same data, you could extract your final QlikView table from either of them, but in this example the sheet using the single horizontal dimension can then be parsed using a single pass of the Crosstable() function, and offer the less complicated syntax.

Use the following to load your “vertical” or “non-pivoted” format of this data into QlikView:


[Import_Cross]:
CrossTable(Date, Data, 3)
LOAD *
FROM
(
ooxml, embedded labels, header is 2 lines, table is Sheet1);

//this second pass at the table is only necessary

//if you want to fully populate Resource_ID
//if not you can skip the second load

[FINAL]:
NOCONCATENATE LOAD If(Len(F1)>0,F1, Peek(Resource_ID)) AS Resource_ID,
Resource, [Record Type], Date, Data RESIDENT [Import_Cross];

DROP TABLE [Import_Cross];

 

 

I noticed on the Excel sheet [Sheet1] that Column A is a numeric [Resource ID] and is only populated on the first row of each new [Resource] value.  In my example I keep [Resource ID] in your final table, and populating 100% where entries would have been null. 

Other options would be to load the [Resource ID] into a separate dimension table, connecting to fact table on [Resource], or just dropping the field [Resource ID] altogether if not necessary for visualization.

The output looks like this:

   

 

With this table structure in your QlikView data model, you should be able to reform both of the pivot structures shown in both tabs of your spreadsheet (using QlikView Pivot Tables), while still storing the data in the most versatile non-pivoted form. 

 

Anonymous
Not applicable

Hi,


Here is the attached application .

With the mapping of two sheets.

I hope this helps you .

Regards,

Bunny

gshockxcc
Creator
Creator
Author

Caleb,

     This was very helpful as well.  There was one minor change I wanted to make so that I had 3 Qualifier fields instead of only 2.  But this is exactly what I was looking for.  I tried the Crosstable on my own before posting, but I couldn't get the result I was looking for.  I really appreciate the help.

gshockxcc
Creator
Creator
Author

Evan,

     It took me some time to understand the second load statement for Resource_ID.  Now it's clear to me.  In this case, it's an artifact of the program used to export the data, but your understanding of it is exactly correct.  For my team's needs, it's not relevant.  But this is a good trick to use for other data that does have this same pattern.

Thanks much.

-Kristan

gshockxcc
Creator
Creator
Author

Bunnyqlik,

    As I mentioned for the other posters, this was really helpful.  Crosstables are not something that I have been very successful with, and this helps me gain a better understanding of when/how to use them.

Thank you for your help.  It is greatly appreciated.