topic Re: Cross Table in QlikView

Cross Table

Anonymous — Wed, 18 Mar 2015 15:36:44 GMT

I have attached file and are still seeking clarification from customer on why the different fields however herewith my problem. The attached sheet CTD on file Order is a table and I need this in data fields.

Column A is ProductCode1

Column B is ProductCodeDescription

Column C is ProductCode2

Row 1 is PO

Row 2 is MannufactureDt

Row 3 is RequiredDt

and then the relevant values

My end result need to be

I have tried cross table but not able to get a workable solution.

Please assist - below the file.

Re: Cross Table

hic — Wed, 18 Mar 2015 18:00:20 GMT

For each vColumn in 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q'

PO:

Load

'$(vColumn)' as Column,

$(vColumn) as PO

FROM [Order.xlsx] (ooxml, no labels, header is 0 lines, table is CTD)

Where RecNo() = 1 ;

MDate:

Load

'$(vColumn)' as Column,

Date($(vColumn)) as MannufactureDt

FROM [Order.xlsx] (ooxml, no labels, header is 0 lines, table is CTD)

Where RecNo() = 2 ;

RDate:

Load

'$(vColumn)' as Column,

Date($(vColumn)) as RequiredDt

FROM [Order.xlsx] (ooxml, no labels, header is 0 lines, table is CTD)

Where RecNo() = 3 ;

LOAD

'$(vColumn)' as Column,

A as ProductCode1,

B as ProductCodeDescription,

C as ProductCode2,

$(vColumn) as Value

FROM [Order.xlsx] (ooxml, no labels, header is 3 lines, table is CTD);

Next vColumn

/HIC

Re: Cross Table

Gysbert_Wassenaar — Wed, 18 Mar 2015 18:41:29 GMT

Cool. That's an interesting approach. But I think it wouldn't work so nice if the 'header' cells in the excel source file contain values in merged cells spanning several columns. I've attached another approach that uses a Transpose() and will deal with merged cell values. Unfortunately there's still a bug somewhere since I get a record too much using the source file of this discussion due to the last total line.

Re: Cross Table

hic — Wed, 18 Mar 2015 18:45:40 GMT

This is one of the things I love with QlikView: There is always more than one way to skin a cat...

HIC

Re: Cross Table

Anonymous — Wed, 18 Mar 2015 18:48:26 GMT

Henric,

this worked well - just a lot of data clean-up.

Re: Cross Table

Anonymous — Wed, 18 Mar 2015 18:49:02 GMT

Gysbert.

there was an initial reply with a model that disappeared. Can you repost.