Cross Table

[Anonymous]

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

Column A is ProductCode1 |  ProductCodeDescription | ProductCode2 | PO | MannufactureDt | RequiredDt | Value

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

Please assist - below the file.

[hic]

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

[Gysbert_Wassenaar · Accepted solution]

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.

[hic]

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

HIC

[Anonymous]

Henric,

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

[Anonymous]

Gysbert.

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