Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Gysbert_Wassenaar

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
hic
Former Employee
Former Employee

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

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
hic
Former Employee
Former Employee

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

HIC

Anonymous
Not applicable
Author

Henric,

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

Anonymous
Not applicable
Author

Gysbert.

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