Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
juanenslin
New Contributor II

Cross Table

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.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Cross Table

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.


talk is cheap, supply exceeds demand

View solution in original post

5 Replies

Re: Cross Table

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

MVP & Luminary
MVP & Luminary

Re: Cross Table

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.


talk is cheap, supply exceeds demand

View solution in original post

Re: Cross Table

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

HIC

juanenslin
New Contributor II

Re: Cross Table

Henric,

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

Highlighted
juanenslin
New Contributor II

Re: Cross Table

Gysbert.

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