Create a new column based on another column to be used as a dimension from

[#1]

I have a file that I'm loading and it contains sales item information.  There are several fields in the file that are quantity fields and I would like to somehow assign a new column name that I can use as a dimension instead of as an expression.

Here is a partial list of the fields:

sales_detail:

LOAD YR_PRD,

     FYEAR,

     FPERIOD,

     COMPANY,

     SALES_ORDER,

     SO_POS,

     ALLOC_TYPE,

     ITEM_GROUP,

     CUNO,

     text(ITEM) as ITEM,

     MFG_SOURCE,

     NO_UNITS,         

     SALES_AMNT,

     REV_ACES_DEF,

     REV_ACES_REC,

     REV_REBATES,

     REV_ALLOC,

     REV_TOTAL,

     BOM_COST,

     OTH_COGS

I want to create a new column for the last 9 fields so that I can use them in a pivot table

so for NO_UNITS I want the column to be called 'Shipment Qty'

           SALES_AMNT called 'Gross Sales',

            REV_ACES_DEF called 'Deferred Billings', etc.

The columns have to be created like this because I will be using the pivot table to do some comparisons.

I have attached an excel spreadsheet to show how the qlkiview document would need to look like.

[Gysbert_Wassenaar]

You don't have to rename fields to use them as dimensions. So I'm probably misunderstanding your question. I'm afraid your excel does not make things clear for me. Can you post an example of the source data and which calculations you want performed on which columns?

[Roop]

Hi ,

     NO_UNITS as 'Shipment Quantity',

     SALES_AMNT as 'Gross Quantity',

     REV_ACES_DEF as 'Deferred Billings',

etc.

would allow you to rename the columns

QlikView makes no diferentiation between a numeric field or any other and they can all be used in expressions and dimensions. If you wished to make some form of differentiation you could also rename them differently although this is not usually done. So you could have the following if you wanted:

     NO_UNITS as 'Shipment Quantity',

     NO_UNITS as 'Shipment Quantity Dimension',

     SALES_AMNT as 'Gross Quantity',

     SALES_AMNT as 'Gross Quantity Dimension',

etc.

I hope that this helps

[#4]

here's a sample of the data coming in

I need to get the sum of each of the value fields for the item and I want it broken down using the names listed on the excel spreadsheet so I want the names to be set up to be used as dimensions.

[Gysbert_Wassenaar]

See attached qvw. Is that what you need?

[#6]

Somewhat.  I would need the fields grouped for each item vertically because I will be doing comparisons with them for certain fiscal periods.

for instance:

                                                                                201501                           201502

Item      Shipment Qty                                             xxxxx                               xxxxx

              Gross Sales                                              xxxxx                                xxxxx

             ACES/Care Deferred Billings                     xxxxx                                xxxxxx

             ACES/Care Revenue Recognition             xxxxx                                xxxxxx

             Rebates                                                      xxxxx                                xxxxxx

             Allocation Adjustments                               xxxxx                                xxxxx

etc.

then

item again with same breakdown

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.