topic Re: Mapping Load with Crosstab in QlikView

Mapping Load with Crosstab

Mon, 26 Aug 2013 15:54:58 GMT

Hello. I'm trying to set up a mapping load using CROSSTAB. My source is an Excel file. What I need is to pull labels from the second line of the file and have those paired with the letter associated with the column. So my Excel file might look like this:

I need a mapping table of
A, Apple
B, Bonnie, etc.

My initial attempt (without the MAPPING LOAD) was something like this:
CrossTable(xColumn, xValue)
FIRST 63 LOAD * from [Main.xlsx](ooxml, explicit labels, table is Sheet1);

63 is the number of columns. This works but gives me a third column (in first position) which I don't need and prohibits it from becoming a MAPPING LOAD.

Can someone see a better way? Thanks.

Re: Mapping Load with Crosstab

Anonymous — Mon, 26 Aug 2013 15:58:50 GMT

Brian,

I recommend to separate these steps:

1 - crossatable load into a table.

2 - create map:

Map:

MAPPING LOAD DISTINCT

Field1,

Field2

RESIDENT table;

Regards,

Michael

Edit: missed MAPPING 😞

Re: Mapping Load with Crosstab

Gysbert_Wassenaar — Mon, 26 Aug 2013 16:06:47 GMT

Try using the Transpose transformation instead:

MyMap:

mapping LOAD @1 as xColumn,

@3 as xValue

FROM

[Main.xlsx](ooxml, no labels, table is Sheet1, filters(

Transpose()

));

Re: Mapping Load with Crosstab

Mon, 26 Aug 2013 18:20:30 GMT

Thanks. TRANSPOSE() is a good idea. However, instead of the column headers and the second data row, this is giving me the first and third data rows...

Re: Mapping Load with Crosstab

Mon, 26 Aug 2013 18:21:38 GMT

Thanks. I'll fall back on this if I can't get transpose to work...

Re: Mapping Load with Crosstab

Gysbert_Wassenaar — Mon, 26 Aug 2013 18:35:28 GMT

Did you specify no labels as I did or do you still have embedded labels set?

Re: Mapping Load with Crosstab

Mon, 26 Aug 2013 18:39:16 GMT

Thanks. I tried both "no labels" and "explicit labels" -- same result.

"embedded labels" raises an error:

Field not found - <@1>

Re: Mapping Load with Crosstab

Gysbert_Wassenaar — Mon, 26 Aug 2013 18:46:54 GMT

Post the excel file please. Or at least a file with the exact same structure.

Re: Mapping Load with Crosstab

Mon, 26 Aug 2013 19:03:12 GMT

I don't see an option to upload -- but here's a masked version of the file. It's pretty straightforward. I need a mapping of:

M, Easy

N, Timely

O, Comprehensive

P, Accurate

Do I not have enough merit points yet to upload files?

Re: Re: Mapping Load with Crosstab

Anonymous — Mon, 26 Aug 2013 19:18:20 GMT

It's not about the merit points... Click "use advanced editor", it is on the top right of the reply window. After this, you can see "attach" on the bottom right.

Regards,

Michael

Re: Mapping Load with Crosstab

Gysbert_Wassenaar — Mon, 26 Aug 2013 19:19:11 GMT

No, just click on the Use advanced editor link (top right of the Reply box).

I see your problem. The column letters from excel can't be used after transposing. You'll have to do as Michael said above. First use Crosstable then do a resident load from the new table.

Re: Mapping Load with Crosstab

Tue, 27 Aug 2013 12:22:33 GMT

Thank you all for your time. I'll start looking at Michael's approach.

(I found the Attach option, by the way. I was looking in the menu bar.)