topic Re: How to load a crosstable with two header rows/2 attribute fields. in QlikView

How to load a crosstable with two header rows/2 attribute fields.

richard_cioci — Tue, 02 Jun 2015 16:01:48 GMT

I'm trying to load a table from Excel which we receive with 2 header rows. I can't change the source file when we get it each time, so I'm trying to figure a way to do a cross table with the 2 header rows which both have necessary information.

I've included a sample xls of the form of my source data and what I want it to load it in as.

To add to that, the top header is merged meaning that those columns are empty when loaded, so there also needs to be a transform to fill that header in.

Also, months change according to the last 10 months( or as in the sample, 5 months) of each product, so I can hardcode field names, for the dates, it has to be an * load.

I.e. next month, the last first month will be feb-15 and the last will be jun-15.

Any help is appreciated

Re: How to load a crosstable with two header rows/2 attribute fields.

richard_cioci — Tue, 02 Jun 2015 17:28:09 GMT

This didn't work either, see post below, but I'll leave it here for reference:

So I've managed to figure it out through a bit of a workaround including transposing the table, combining the headers which are now columns into a single key in a single column, then exporting to csv, then re-importing and transposing back, export and reimport and then use crosstable function on the single key. Once that's done, the newly created column with the key is split back into two columns with the two pieces of information.

Not the best way though, so if someone has a simpler or more elegant way please let me know.

Luckily one of my keys was always the same length, so it made splitting up the keys pretty easy, do we know if there's a way to join and unjoin a key in a delimited way for future just in case scenarios?

i.e. record 1 is 23, abd, 78. After I join them like F1& '-' &F2 & '-' &F3. Is there a function to separate them based on hyphens?

But yea, if someone has a simpler method to load in a crosstable with two headers, please let me know.

Re: How to load a crosstable with two header rows/2 attribute fields.

richard_cioci — Tue, 02 Jun 2015 17:31:00 GMT

Just realized that didn't work well either because now the original columns have number add ons, i.e. Ontario, Ontario1, Ontario2, from when I transposed them and they became headers (Qlik auto adds numbers when multiple headers have the same name).

Re: How to load a crosstable with two header rows/2 attribute fields.

swuehl — Tue, 02 Jun 2015 17:36:56 GMT

Maybe something like attached?

Re: How to load a crosstable with two header rows/2 attribute fields.

richard_cioci — Tue, 02 Jun 2015 17:39:39 GMT

Yes, like that.

Can you explain at a high level what you did to get this? I'm looking at the code and trying to make sense so I can apply it properly to bigger and different data sets.

Re: How to load a crosstable with two header rows/2 attribute fields.

swuehl — Tue, 02 Jun 2015 17:45:17 GMT

//Create a Mapping for the first row, Product categories

MAP1:

MAPPING

First 2

LOAD iterno() as ID,

if(iterno()=1,C,H) as Cat

FROM

(ooxml, no labels, table is Sheet1)

while iterno() <=2;

//Create a Mapping for your column header containing Dates

TMP:

CROSSTABLE (Field, Date) LOAD

1 as Dummy,

FROM

(ooxml, no labels, table is Sheet1)

Where recno() = 2;

MAP2:

MAPPING LOAD Field, Date Resident TMP;

//NOTE: to have a look at the mapping tables, remove the MAPPING from the LOAD prefix, and exit script; at this point

DROP TABLE TMP;

// Prepare your Crosstable load, load in data for the two categories

TMP3:

LOAD A as Province,

B as City,

applymap('MAP1',1) as ProductCat,

FROM

(ooxml, no labels, table is Sheet1)

where recno() >2;

TMP3:

CONCATENATE LOAD A as Province,

B as City,

applymap('MAP1',2) as ProductCat,

FROM

(ooxml, no labels, table is Sheet1)

where recno() >2;

// Do the actual crosstable load

CROSS:

CROSSTABLE (Date, Value, 3)

LOAD * Resident TMP3;

drop table TMP3;

// Now you've got your crosstable transformed in a flat table, but Date values are still column names C,D,E,F,... instead of the header values.

//Now finally, map your dates to column names

RESULT:

NOCONCATENATE

LOAD Province, City, ProductCat, Date(applymap('MAP2',Date),'MMM YY') as Date, Value

RESIDENT CROSS;

DROP TABLE CROSS;

Re: How to load a crosstable with two header rows/2 attribute fields.

richard_cioci — Tue, 02 Jun 2015 17:59:37 GMT

Thanks for your help, much appreciated

Re: How to load a crosstable with two header rows/2 attribute fields.

richard_cioci — Tue, 02 Jun 2015 18:40:04 GMT

So I had a bit of trouble following applying the solution myself on a larger scale. I think I got confused by the several maps. I'm still a beginner.

So in the meantime, i found a solution that's simpler than my first and easier for me to follow/apply.

Basically, I cleaved the data table into two, one table is headers, and one is the data, each with automatic column naming. Then transposed, combined the headings into one column and created a map from that with the automatic naming as @1, @2, so on. Then applied that map the the data using rename fields function, and then cross table'd that, and then split the two columns up.

Everything looks great now.

Re: How to load a crosstable with two header rows/2 attribute fields.

raadwiptec — Fri, 01 Jul 2016 14:15:18 GMT

Hi Swuehi,

If theire are 12 product categories how doe we manage in iteration?

Re: How to load a crosstable with two header rows/2 attribute fields.

richard_cioci — Tue, 05 Jul 2016 14:45:04 GMT

Can you show a brief smaller example? Have you tried my solution from my very last post in this thread currently (June 2, 2015)?

Re: How to load a crosstable with two header rows/2 attribute fields.

ingoniclas — Tue, 19 Sep 2017 07:24:15 GMT

Great post with a fantastic solution, Stefan. I have used parts of your solution and almost () solved a similar problem as mentioned in this post. However, the remaining issue: I can't use your mapping solution to change my header since I need to load multiple tables in a loop, i.e. my header columns F, H, J each would have multiple rows with multiple dates, and the mapping would only work with the first date in the first row. Any ideas how to solve this issue?

I have also posted my issue in more detail here. It would be a blast if this could be solved.

Re: How to load a crosstable with two header rows/2 attribute fields.

RobertTimm — Fri, 20 Apr 2018 21:47:37 GMT

Just what I was looking for. Thanks