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

[richard_cioci]

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

[richard_cioci]

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.

[richard_cioci]

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

[swuehl]

Maybe something like attached?

[richard_cioci]

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.

[swuehl · Accepted solution]

//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,

  C,

     D,

     E,

     F,

     G,

     H,

     I,

     J,

     K,

     L

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,

     C,

     D,

     E,

     F,

     G

FROM

(ooxml, no labels, table is Sheet1)

where recno() >2;

TMP3:

CONCATENATE LOAD A as Province,

     B as City,

     applymap('MAP1',2) as ProductCat,

     H,

     I,

     J,

     K,

     L

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;

[richard_cioci]

Thanks for your help, much appreciated

[richard_cioci]

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.

[raadwiptec]

Hi Swuehi,

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

[richard_cioci]

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