Pivot Table Not Displaying All the Data

[#1]

I have two separate files; one is for all orders and the other is like a detail file.

The order file contains orders with transaction date.

I am trying to get the pivot table to sort by transaction date then order number, then operation

the other information will come from the detail file.  I would basically have multiple records for the production, operation.

For some reason, I am unable to display all of the transaction dates in the table.

Here's an example of the data in the order file:

TransDate  order# operation

9/13/2013 11111 10

9/13/2013 11111 20

9/13/2013 12132 10

9/13/2013 11144 20

9/15/2013 21513 15

9/15/2013 21513 20

9/15/2013 23151 15

9/1/2013 25461  10

my detail file would look like this:

order# oper amt    gl trans date

11111 10  15.00   10/15/2013

11111 10   .75      10/15/2013

11111 20  20.00    11/25/2013

12132 10 1.50      12/1/2013

11144  5   1.25      12/2/2013

11144  10   .65       12/2/2013

I want my table to have all the trans dates listed and then the user clicks on the trans date to see all the orders for that date

but for some reason all of the trans dates aren't showing up.

For example:

9/1/2013 data is not showing up in the pivot table.

Any suggestions on why this might not be showing up?????

Here is the script for the entire load:

prod_orders:

LOAD ORDER_NUMBER,

     ITEM_NUMBER,

     COMPANY,

     OPERATION,

     SEQ_NO,

     WORK_CENTER,

     TASK,

     MAN_HRS,

     MACHINE_HRS,

     LABOR_COSTS,

     OVERHEAD_COSTS,

     TRANSACTION_DATE,

     Month(TRANSACTION_DATE) AS FISCAL_PERIOD,

     Year(TRANSACTION_DATE) AS Year,

     OPERATION_RATE_CODE,

     COST_PRICE_COMP_OH,

     COST_PRICE_COMP_LABOR,

     ORDER_NUMBER & ' ' & OPERATION as ORD_KEY;

SQL SELECT *

    from AD_PROD_OP_HRS;

gl_details:

LOAD ORDER_NUMBER,

     GL_OPERATION as OPERATION,

     GL_COST_PRICE_COMP,

     GL_COMPANY,

     GL_TRANSACTION_DATE,

     GL_AMOUNT,

     GL_POST_DATE,

     GL_NO_OF_UNITS,

     PROD_GL_CPC,

     ORDER_NUMBER & ' ' & GL_OPERATION as ORD_KEY;

SQL SELECT *

    from AD_PRODUCTION_GL_HRS;

[Gysbert_Wassenaar]

To begin with your transaction date fields have different names so they are different fields as far as Qlikview is concerned. You will never see values from Field B if you use Field A as a dimension in a chart. Only values that exist in Field A will be shown. Try renaming one of the transaction date fields so they're named the same and become one field. Then you can enable the Show All Values option if you use that field as a dimension in your pivot chart.

[#3]

The transaction date fields are supposed to be different so that's why they have different names.

The GL_transaction_date in the gl_detail file is totally different from the transaction_date in prod_orders.

I only want to have the transaction_date from prod_orders to be the dimension.

I'm attaching how the records looks so maybe you can get an idea of what I'm trying to do.

The aces1 spreadsheet would be the gl_details file. There are two orders in this file.

The aces2 spreadsheet would be the prod_order file.

I have 3 order numbers in this file.

So based on the records, my pivot table should show transaction dates 9/23/2013 and 9/26/2013 and when the user collapses 9/23/2013 the orders 101914 and 111523 should show.

[Gysbert_Wassenaar]

Perhaps something like attached qvw.

[#5]

I see the error in my ways.  I needed to do the sum on my expressions so now I see the records the way that I want to see them.

Thanks!!!!